Problem with Rank Formula (returning #Value!)

[Shales]

Morning everyone!

I have a question regarding the Rank Formula. I am using this formula;

=RANK('Pet TPL log'!BV3,'Pet TPL log'!$BV$3:$BV$511,1) - but it returns #Value!

The column I am trying to rank is a formula field that will contain numbers like 1.1, 2.5, 2.4, 5.1 etc

Is there a problem with what I am doing here?

Thanks as always
Gavin

 

[FormR]

Hi,

What is the formula that returns the numbers you want to rank?
Do you have any #VALUE! errors in the range 'Pet TPL log'!$BV$3:$BV$511?

 

[Shales]

No #Value! in the range. It has either a number like mentioned above or a blank value as in ""

 

[FormR]

Ranking a blank value ("") will return a #VALUE! - is it only these where you have the problem?

 

[Shales]

Ok, thank you. I'll play around with some other options.

Regards
Gavin

 

[FormR]

You could try, for example:

=IF(BV3="","",RANK(BV3,$BV$3:$BV$511,1))

 

[Shales]

This sadly just returns "" I think. There are no numbers being listed in ascending order.

 

[FormR]

Is it only the cells that are returning "" that generate #VALUE! ?

If no - then what does this return when pointed at a cell with a number you want to rank ?
=ISNUMBER('Pet TPL log'!$BV$3)

If yes - then how exactly would you want to rank a "" ?