How can I make RANK formula work when N/A introduced?

[Ironman]

Hi

Here's the my existing formula

IF(COUNTA('Daily Tracking'!AQ67:AQ73),SUM('Daily Tracking'!AQ67:AQ73),"")

The RANK function (in the row below) works:

RANK(AN47,$B$47:$BZ$47))

The problem is that I must replace the "" in the first formula with #N/A else the chart the formula is linked to will show zero values where there is no data yet, which I don't want. However, when I do that i.e.

IF(COUNTA('Daily Tracking'!AQ67:AQ73),SUM('Daily Tracking'!AQ67:AQ73),#N/A)

then the RANK row below it won't work.

I've tried to adapt the solutions in this old thread, but they give incorrect results and I don't understand how they work to amend them.

Hope you can help?

Many thanks!

 

[kvsrinivasamurthy]

Try

=IF(COUNTA('Daily Tracking'!AQ67:AQ73),SUM('Daily Tracking'!AQ67:AQ73),NA())

 

[Ironman]

Hi, thanks for the suggestion but that made no difference.

 

[Ironman]

I've got it to work by replacing the RANK formula with this formula

IF(ISNUMBER(AN47),COUNT(IF(ISNUMBER($B$47:$BZ$47),IF($B$47:$BZ$47>AN47,1)))+1,"")

Credit, thanks and appreciation to Barry Houdini.