ExcelBanter - Zero Value Ranked

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Zero Value Ranked (https://www.excelbanter.com/excel-worksheet-functions/110898-zero-value-ranked.html)

Bsmile

Zero Value Ranked

I need to rank the "% of inc" column but I get an error message (#N/A) on the
ones that have 0%. This is the formula I'm using:
=IF(H6=0,"",RANK(H6,$H$5:$H$15)). I've also tried
=IF(G21=0,"0%",(C21-G21)/G21). What would you suggest?

% of inc Rank
-100% 6
0% #N/A
300% 1
-33% 3
-100% 6
0% #N/A
-100% 6
0% #N/A
-50% 5
50% 2
-33% 3

Mark

Zero Value Ranked

Bsmile wrote:
I need to rank the "% of inc" column but I get an error message (#N/A) on the
ones that have 0%. This is the formula I'm using:
=IF(H6=0,"",RANK(H6,$H$5:$H$15)). I've also tried
=IF(G21=0,"0%",(C21-G21)/G21). What would you suggest?

try nesting another If statement to place a 0 instead of a #NA.

=IF(ISERROR((C21-G21)/G21),0,(C21-G21)/G21)

Mark

Zero Value Ranked

Disregard the last one, this is what I meant to send:

try this:

=IF(G21=0,"0"%,IF(ISERROR((C21-G21)/G21),0,(C21-G21)/G21))

Bsmile

Zero Value Ranked

Thank you. That worked!

"Mark" wrote:

Disregard the last one, this is what I meant to send:

try this:

=IF(G21=0,"0"%,IF(ISERROR((C21-G21)/G21),0,(C21-G21)/G21))

All times are GMT +1. The time now is 05:42 AM.