=LARGE and cells equaling 0

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 ?
 
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
 
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.
 
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.
 
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...
 
Never mind I got it, I had created a circular reference in the previous step.
Thanks to all for the help!
 
Back
Top