03-30-2010 04:36 PM
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
any ideas?
please and thanks
John
Solved! Go to Solution.
03-30-2010 04:41 PM
I should also say the if the Ref in is for example 1.17 then the out put should be 7
thanks
03-30-2010 04:51 PM - edited 03-30-2010 04:55 PM
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.
03-30-2010 04:53 PM - edited 03-30-2010 04:54 PM
03-30-2010 05:37 PM
Legends!! i was really over thinking this lol
cheers
03-30-2010 07:19 PM
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?
03-30-2010 08:09 PM - edited 03-30-2010 08:13 PM
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.