question

[A.S.]

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.

[VBA Noob]

You could return the number a copy of ways

=LARGE(A:A,1)

=MAX(A:A)

=MIN(A:A)

VBA Noob

[A.S.]

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
>
>

[A.S.]

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
> >
> >

[VBA Noob]

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