Find cell reference in an column of numbers?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Geoff C - 18 Oct 2006 15:39 GMT

I have an column of numbers, e.g.

a1 - 0.03
a2 - 0.17
a3 - 0.35
a4 - 0.54
a5 - 0.67
a6 - 0.83
a7 - 1.00

What I'd like is a formula that returns the cell reference of the first
number in the column that is above a specific number (say, 0.5). Can anyone
suggest a formula that would achieve this?

Many thanks,
Geoff.

Reply to this Message

GRM - 18 Oct 2006 15:55 GMT

Assuming the column of numbers is sequentially ascending, place 0.5 in cell
b1 and try =MATCH(b1,a1:a7,1)+1

>I have an column of numbers, e.g.
>
[quoted text clipped - 12 lines]
>Many thanks,
>Geoff.

Reply to this Message

Geoff C - 18 Oct 2006 16:21 GMT

Thanks!

> Assuming the column of numbers is sequentially ascending, place 0.5 in cell
> b1 and try =MATCH(b1,a1:a7,1)+1
[quoted text clipped - 15 lines]
> >Many thanks,
> >Geoff.

Reply to this Message

Alan Beban - 18 Oct 2006 19:13 GMT

> Assuming the column of numbers is sequentially ascending, place 0.5 in cell
> b1 and try =MATCH(b1,a1:a7,1)+1
[quoted text clipped - 15 lines]
>> Many thanks,
>> Geoff.

For the actual cell reference you might modify it as

="A" & MATCH(b1,a1:a7,1)+1

Alan Beban

Reply to this Message