=LARGE and cells equaling 0

M

M.A.Tyler

Column "F"
57.83
0
60485
57.83
0
0

When I try =LARGE(F1:F6,1) the formula returns 0 ? Why is that, more
importantly how can I fix it ?
 
R

Rick Rothstein \(MVP - VB\)

Column "F"
57.83
0
60485
57.83
0
0

When I try =LARGE(F1:F6,1) the formula returns 0 ? Why is
that, more importantly how can I fix it ?
Click to expand...

It works for me using your data (as long as all those values are numbers,
not text). Are you non-zero values formatted as text by any chance?

Rick
 
T

T. Valko

Hmmm...

If all the values where formatted as TEXT then the formula would return an
error. So, it appears that for whatever reason all the non-zero entries are
being ignored. They maybe text with leading/trailing spaces that can't be
seen. Test the cell holding 60485 to see if it is in fact a numeric number:

=ISNUMBER(F3)

The result should be TRUE. If the result is not TRUE then you need to look
at either the formatting or look for unseen characters.
 
R

Ragdyer

You 0's are numbers, and your numbers are text.
If all your data were text, you'd get the #NUM! error.

Right click on a new, unused, empty cell, and choose "Copy".

Select all your numbers.
Right click in the selection, and choose "Paste Special",
Click on "Add", then <OK>.

And that should make your numbers true, XL recognized numbers, and your
formula should work as expected.
 
M

M.A.Tyler

Tryed that, Now I've got the #NUM! error?

Ragdyer said:
You 0's are numbers, and your numbers are text.
If all your data were text, you'd get the #NUM! error.

Right click on a new, unused, empty cell, and choose "Copy".

Select all your numbers.
Right click in the selection, and choose "Paste Special",
Click on "Add", then <OK>.

And that should make your numbers true, XL recognized numbers, and your
formula should work as expected.
Click to expand...
 
M

M.A.Tyler

Never mind I got it, I had created a circular reference in the previous step.
Thanks to all for the help!
 
Top