how to apply a "RANK" formula

[Pauls123]

Hi,

A new person to this forum, this is my second question please. I have a column in my excel 2010 spreadsheet AC which contains a series of ratings. The formula I have in cell AC2 is:

=IF(J2="x","",R2-AB2-Q2-S2)

The pertinent factor here is that if I have an "x" in cell J2 then cell AC2 comes up blank, which is what I want. And so forth for all of Column AC.

In my column AD2 I have a formula of:

=RANK(AC2,$AC$2:$AC$20,0)

The rankings work perfectly for all of column AD where I have numbers in column AC, but where there are blanks I get the #VALUE!

These are of course because I have an "x" in Column J in the cell/s.

How can I fix up my formula in Column AD to just show a blank when I dont want a figure there,..??

Regards, Paul

 

[T. Valko]

Hi,

A new person to this forum, this is my second question please. I have a column in my excel 2010 spreadsheet AC which contains a series of ratings. The formula I have in cell AC2 is:

=IF(J2="x","",R2-AB2-Q2-S2)

The pertinent factor here is that if I have an "x" in cell J2 then cell AC2 comes up blank, which is what I want. And so forth for all of Column AC.

In my column AD2 I have a formula of:

=RANK(AC2,$AC$2:$AC$20,0)

The rankings work perfectly for all of column AD where I have numbers in column AC, but where there are blanks I get the #VALUE!

These are of course because I have an "x" in Column J in the cell/s.

How can I fix up my formula in Column AD to just show a blank when I dont want a figure there,..??

Regards, Paul

Try this...

=IF(AC2="","",RANK(AC2,$AC$2:$AC$20))

 

[indiantrix]

Maybe try this modification:
=IFERROR(RANK(AC2,$AC$2:$AC$20,0),"")

 

[Pauls123]

Thanks very much for both those answers. I tried them both, the first answer didnt work, it left the formula sitting there in Cell AD2, just sitting there and doing nothing.

However the second answer worked perfectly, I am impressed.

Thanks again, Paul

 

[indiantrix]

You're welcome, Paul. I'd bet that if we stick around here long enough, we'll get a few more workable answers, perhaps some even more correct than these! This is, after all, the answer place, eh?
Larry.

 

[T. Valko]

Thanks very much for both those answers. I tried them both, the first answer didnt work, it left the formula sitting there in Cell AD2, just sitting there and doing nothing.

However the second answer worked perfectly, I am impressed.

Thanks again, Paul

Hmmm...

Did you try both formulas in the same cell?

I can assure you that the formula I suggested does what you want.