Rank function producing #N/A result

[Sc0tt1e]

Can anyone explain to me why the attached list will not rank?

[JieJenn]

Because right now the formatting is still "text". Highlight column A, then do a TextToColumns (Data ribbon). Your calculation is set to Manual, you might want to change the setting to automatic.

[Sc0tt1e]

When I do that it defaults the last 8 digits to 0???

[Pete_UK]

Well, the RANK function only works on numbers, not text values that look like numbers, and numbers only have a precision of 15 digits in Excel (and many other software packages). You can sort the data as it is, and then it will be ranked, so you can allocate a simple sequence to generate the rank.

Hope this helps.

Pete

[JieJenn]

I have a feeling Excel cannot handle that large of numeric number. Hopefully someone else can advise.

[martindwilson]

you could try
=SUMPRODUCT(--($A$2:$A$50>=A2))
or
=SUMPRODUCT(--($A$2:$A$50<=A2))