row number of a range of values

[josh-mediwatch]

If I have a list of value ranging from 0-30 in column A sorted into order with say 150 results I want to know what row number has the first occurrence of 0.5 and then the last occurrence of 25 so i can just look at the range of values.

Any idea if it is possible to display the row number?

[XOR LX]

Hi,

Assuming you mean that they're in ascending order:

=MATCH(0.5,A1:A150,0)

=MATCH(25,A1:A150)

Regards

[Fotis1991]

1)

Please Login or Register  to view this content.

2) ARRAY formula.

Please Login or Register  to view this content.

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[josh-mediwatch]

is there a way if you dont have an exact 0.5 or 25 so it find the range greater than 0.5 and less than 25?

[Fotis1991]

You are welcome..

Please Login or Register  to view this content.

[josh-mediwatch]

Many thanks for your help so far

Ive got it working to find the row number displaying the first result equal to or greater than 0.5 using =MATCH(TRUE,INDEX($B$2:$B$170>=0.5,0),0

the problem I'm find is I want to find the last row that has the last number equal to or less than 25 any ideas?

[Fotis1991]

Please Login or Register  to view this content.

ARRAY formula.

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[josh-mediwatch]

that finds the first row with a value less than 25 not the last

[Fotis1991]

If you take some time to read the instructions that i give you in my previous post about ARRAY formula, you reply would be different than this.