for loop wont loop through array built from spread sheet

[Canofsteam]

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

[Canofsteam]

I should also say the if the Ref in is for example 1.17 then the out put should be 7

thanks

[altenbach · Accepted 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. 

Message Edited by altenbach on 03-30-2010 02:55 PM

[Mark_Yedinak]

You are only reading the first column of every row. Add an input to set your delimitor to a comma (',') and you will read all the data. BTW, your loop is executing correctly. It is running once since only a single column is read. Loop count is zero based.

Message Edited by Mark Yedinak on 03-30-2010 04:54 PM

[Canofsteam]

Legends!! i was really over thinking this lol

cheers

[altenbach]

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?

[altenbach]

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.

Message Edited by altenbach on 03-30-2010 06:13 PM