RANK and IF formula problem

[bigpat]

Hi,

I have 10 "categories" running several processes which can either pass or fail, so I have a calculation showing the pass rate which can range from 0% to 100%. I also need to rank the categories in order of that pass rate and apply conditional formatting to show the best and worst. I can make this work if all categories have at least one completed process.

But in some datasets, there may be no processes at all for a particular category. In this case I want to show an "empty" result rather than 0% but then the RANK formula doesn't work. Is there something else I can put after the comma near the end of the IF statement below?

So far I have tried:
"" result = #VALUE! in the corresponding cell in the Rank column
NA() result = #N/A all the way down the Rank column

Or can I do something to the RANK formula (another IF maybe) that would make it exclude empty cells or cells with a particular value?

This forum does not seem to allow attachments and hopefully it will not mess up this formatting. Imagine the following data in cells A1:E11

The formula in D2 (the first pass rate) is =IF(B2+B2>0,B2/(B2+C2),) so what should I put after that last comma?


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Pass[/TD]
[TD]Fail[/TD]
[TD]Pass rate[/TD]
[TD]Rank[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]11[/TD]
[TD]10[/TD]
[TD]52%[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD]46%[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]13[/TD]
[TD]3[/TD]
[TD]81%[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD]73%[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0%[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]80%[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]100%[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]7[/TD]
[TD]1[/TD]
[TD]88%[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD]80%[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]







< < needs to be blank (or "appear" blank via conditional formatting)












Grateful for any suggestions!!

Thanks

 

[Special-K99]

Shouldnt that be

=IF(B2+C2>0,B2/(B2+C2),"")

You need to check that B2+C2, the divisor is not zero else you will get DIV0 errors.

By the way you're right...
You cant attach files on this forum.
You need to upload the file to an online storage site then place a link to it on this forum.

 

[Aladin Akyurek]

...
You need to upload the file to an online storage site then place a link to it on this forum.

Rather not. Attachments lists a few methods for posting sample exhibits and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl
is also a method for posting an exhibit.<strike></strike>

 

[bigpat]

Shouldnt that be

=IF(B2+C2>0,B2/(B2+C2),"")

You need to check that B2+C2, the divisor is not zero else you will get DIV0 errors.

That was the first thing I tried. That works fine in the Pass rate column but then causes a #VALUE! error in the Rank column for category D. That's the scenario I'm trying to handle, where a category has no entries at all. I need the Rank formula to still work either by replacing the "" with something RANK can handle, or changing the RANK formula itself.

 

[bigpat]

Sorry I just realised there was a typo in the formula I gave. Yes it should have been IF (B2+C2...

But that wasn't the issue and with a little thought I figured out the problem. It was almost absurdly simple, but it had been a long day...and I'd had no lunch...and I hate Mondays!!

Anyway, I just needed to leave the Pass Rate formula alone i.e go back to using "" at the end of the IF, but change the Rank formula to =IF(D2<>"",RANK(D2,$D$2:$D$6),"")

That works