Step through a row of data

N

Nick Krill

How can I step through a row of data to find the first entry that exceeds a
reference value?

Reference: 45.57

45.54 45.56 45.32 45.5 45.42 45.35 45.46 45.59 44.97 46 45.05 44.55

The answer would be 8 (45.59)
 
B

B. R.Ramachandran

Hi Nick,

Incorporate actual cell and range references in the following formula (to
suit to your spreadsheet; for example, "refvalue" could be A2, "datarange"
could be A1:L1, and "firstvalue" could be A1) and confirm with
CTRL-SHIFT-ENTER.

=IF(SUMPRODUCT((refvalue<datarange)*1)=0,"Not
exceeded",MIN(IF(refvalue<datarange,COLUMN(datarange)-COLUMN(firstvalue)+1))&"
("&INDEX(datarange,MIN(IF(refvalue<datarange,COLUMN(datarange)-COLUMN(firstvalue)+1)))&")")

Regards,
B. R. Ramachandran
 
Top