im probably doing sonthing really silly but........
first i build and array out of data from a spreadsheet (RefLookup)
the spreadsheet contains 3 rows the top row is my label (to be returned later)
the second row it my maxmum value
the third row is my minimum value.
Ref in is my live feed of data to be looked up.
i then put this into a for loop, to loop through the array untill it finds which range of data the Ref in lies.....
then i simply stop the loop and output the index value.
this index value is the used to look up the spreadsheet data again and return the label for that index.
from what i can gather the code should.... work. but it doesnt seem to go passed the first itteration of the for loop
please and thanks
Solved! Go to Solution.
You need to set the delimiter to comma, else you don't get all data. (read from spreadsheet file)
You are doing this way too complicated. Here's equivalent code. I am sure it can be simplified much more!
You don't need the outer while loop. finding it once is sufficient.
You probably want to add a "select" after the loop that selects NaN instead of the last value if nothing is found, based on the boolean.
Note that there are many things wrong with your code, for example you do comparisons on DBL and your "ranges" are leaky (there are small gaps of width 0.0001 where a match does not occur, but probably should). There is way too much redundant information between the second and third row.
You also need to specify what should happen if no match occurs.
(my rewrite shown above does not address these issues).
As mentioned, this can be simplified much more. You probably don't even need a loop.
Where is this data coming from?
As mentioned, your algorithm has serious flaws. Here's a quick comparison with a more stable algorithm (that does not even use the third row).
You can see how the output depends on the input. You still need to deal with the situation if the input is out of range.