Problem with Rank Formula (returning #Value!)

Shales

Board Regular
Joined
Aug 8, 2006
Messages
168
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,467
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 8, 2006
Messages
168
No #Value! in the range. It has either a number like mentioned above or a blank value as in ""
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,467
Office Version
  1. 365
Platform
  1. Windows
Ranking a blank value ("") will return a #VALUE! - is it only these where you have the problem?
 

Shales

Board Regular
Joined
Aug 8, 2006
Messages
168

ADVERTISEMENT

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

Regards
Gavin
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,467
Office Version
  1. 365
Platform
  1. Windows
You could try, for example:

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

Shales

Board Regular
Joined
Aug 8, 2006
Messages
168
This sadly just returns "" I think. There are no numbers being listed in ascending order.
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,467
Office Version
  1. 365
Platform
  1. Windows
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 "" ?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,470
Messages
5,601,840
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top