I am trying to create a formula so that from a list of say 2000 of which 1999
are "NO" and one will be a digit, what formula can I use so that this one is
selected out of all of them?

Example:
7
No
No
No
No
....
I want to create a formula so that all the cells are checked and it picks
the 7 (or whatever number) because it is not a "NO".

Thanks.

You could return the number a copy of ways

=LARGE(A:A,1)

=MAX(A:A)

=MIN(A:A)

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=568114

Thanks for the help. The Large formula works, however, what-if I have a 3/12
instead of a whole number? I need to be able to pick the 3/12 out of the "NO"?

"VBA Noob" wrote:


You could return the number a copy of ways

=LARGE(A:A,1)

=MAX(A:A)

=MIN(A:A)

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=568114

it has to be 3/12 and not given as a decimal. I am not using the number as a
fraction or a decimal but rather as "3 slash 12"

"A.S." wrote:

Thanks for the help. The Large formula works, however, what-if I have a 3/12
instead of a whole number? I need to be able to pick the 3/12 out of the "NO"?

"VBA Noob" wrote:


You could return the number a copy of ways

=LARGE(A:A,1)

=MAX(A:A)

=MIN(A:A)

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=568114

You could try the below

Just change the range to your range. Also if the target changes then
change "3/12" to a cell address

=INDIRECT(ADDRESS(ROW(*J1:J13*)+MATCH("3/12",*J1:J13*,0)-1,COLUMN(*J1:J13*)))

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=568114