Simon has attended:
Excel Intermediate course
Excel Advanced course
Excel Advanced - Formulas & Functions course
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Excel Advanced - For Power Users course
Lookup first occurance of number
Hi, I would be grateful for some help.
I have a column of numbers and would like to count the number of rows before a specific number (e.g. 38.8) has its first occurrence. The context to this is the amount of time it takes a person to get to a certain temperature if each row is 1 min.
Many thanks
Simon
RE: Lookup first occurance of number
Hi Simon
Thanks for getting in touch.
The simplest option is MATCH. Here's an example:
=MATCH(38.8, A1:A50, 0)
Which will find the value "38.8" in the range of cells A1:A50. The zero ensures an exact match. The formula will return the answer in the form of a number. This number will count how many rows down the range the answer was found.
I hope this helps.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Lookup first occurance of number
Thanks Gary, that works great. If there is not a 38.8 in the range, I get an N/A error what would I need to display the total rows, an if function?
RE: Lookup first occurance of number
Hi Simon
Try the always-useful IFERROR:
=IFERROR(MATCH(38.8, A1:A50, 0),COUNTA(A1:A150))
If the function returns an error, instead count how many entries are in the range and return that instead.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Lookup first occurance of number
That works great, but I think the iferror function was developed for the 2010 version. I need it to be compatible with 2003 for work. Sorry didnt mention that, is there an alternative?
RE: Lookup first occurance of number
Hi Simon
Possible - just a little torturous:
=IF(ISERROR(MATCH(38.8, A1:A50, 0)), COUNTA(A1:A150), MATCH(38.8, A1:A50, 0))
You can see why IFERROR was invented!
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
