top 5 number is LARGE function with N/Afields
 

Hi,
I have a column with a bunch of numbers and some N/A fields. I would
like to find out the top 5 numbers. I tried with
=LARGE((A1:A1000), 1)
=LARGE((A1:A1000), 2)
=LARGE((A1:A1000), 3)
=LARGE((A1:A1000), 4)
=LARGE((A1:A1000), 5)

however the numbers I am getting don't seem to be correct. did the N/A
field affect the function? please advise. thanks.


--
owl527
------------------------------------------------------------------------
owl527's Profile: http://www.excelforum.com/member.php...o&userid=20916
View this thread: http://www.excelforum.com/showthread...hreadid=518559

top 5 number is LARGE function with N/Afields
 

Hi

Yes the N/A is resulting in a N/A error. The following array entered
formula worked for me
{=LARGE(IF(ISNUMBER(A1:A1000),A1:A1000,0),1)}

To array enter, commit or edit with Ctrl+Shift+Enter. Do not type the
curly braces { } yourself , when you use Ctrl+Shift+Enter, Excel will
insert them.

--
Regards

Roger Govier


"owl527" wrote in
message ...

Hi,
I have a column with a bunch of numbers and some N/A fields. I would
like to find out the top 5 numbers. I tried with
=LARGE((A1:A1000), 1)
=LARGE((A1:A1000), 2)
=LARGE((A1:A1000), 3)
=LARGE((A1:A1000), 4)
=LARGE((A1:A1000), 5)

however the numbers I am getting don't seem to be correct. did the N/A
field affect the function? please advise. thanks.


--
owl527
------------------------------------------------------------------------
owl527's Profile:
http://www.excelforum.com/member.php...o&userid=20916
View this thread:
http://www.excelforum.com/showthread...hreadid=518559