Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Step through a row of data
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Step through a row of data
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(dataran ge)-COLUMN(firstvalue)+1))&" ("&INDEX(datarange,MIN(IF(refvalue<datarange,COLUM N(datarange)-COLUMN(firstvalue)+1)))&")") Regards, B. R. Ramachandran "Nick Krill" wrote: 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need more general XY point to point plotting than XY scatter in | Charts and Charting in Excel | |||
Excel 2003, Convert EXISTING Worksheet Data to XML? | Excel Discussion (Misc queries) | |||
Import Data Keeps asking for Password | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |