Formula Audit

[excel_sp]

Hi all,

In my worksheet there are two cells D8 and E8 where I have to input data and the outcome of the formula will be visible on M8 cell.

Below is the tabular representation of data input in cells, and their expected output


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Value in D8[/TD]
[TD]Value in E8[/TD]
[TD]Outcome[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]E[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD]E[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]>=85%[/TD]
[TD]E[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]<85%[/TD]
[TD]E[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]E[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]NA[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD]NA[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]>=85%[/TD]
[TD]NA[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]<85%[/TD]
[TD]NA[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NA[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]>=85%[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]<85%[/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]>=85%[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD]>=85%[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]>=85%[/TD]
[TD]>=85%[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]<85%[/TD]
[TD]>=85%[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]>=85%[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]E[/TD]
[TD]<85%[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD]<85%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]>=85%[/TD]
[TD]<85%[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]<85%[/TD]
[TD]<85%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]<85%[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

The formula that I am using is as under:

=IF(E8="E",IF(OR(D8="E",D8="NA",D8>85%),10,5),IF(E8="NA",IF(OR(D8="E",D8="NA",D8>=85%),10,0),IF(E8="",IF(OR(D8="E",D8>=85%),5,0),IF(E8>=85%,IF(OR(D8="E",D8="NA",D8>=85%),10,5),IF(E8<85%,IF(OR(D8="E",D8>=85%),5,0))))))

With above code, most of the time, the outcome is expected, except in below two situations
[TABLE="width: 500"]
<tbody>[TR]
[TD]Value in D8[/TD]
[TD]Value in E8[/TD]
[TD]Real Outcome[/TD]
[TD]Expected Outcome[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD]60%[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


I am not able to find the error in the formula. Can you please guide me?

Thanks in advance.

 

[Special-K99]

The thing to do in a case like this is isolate the problem.
As you have multiple results for 5 I changed each 5 to a different value 5.1, 5.2, 5.3, 5.4
Result was 5.4
So it's the last condition that's causing this

IF(E8<85%,
IF(OR(D8="E",D8>=85%),5,0))))))

Removing the D8>=85% produces 0

I suspect when D8 is NA it is regarded as >85% and therefore returns 5.
Which means your other % conditions may cause problems like this too.

Confirmed: When D8 is NA, =D8>85% returns TRUE

 

[Special-K99]

This looks better

=IF(E8="E",IF(OR(D8="E",D8="NA",AND(ISNUMBER(D8),D8> =85%)),10,5.1),IF(E8="NA",IF(OR(D8="E",D8="NA",AND(ISNUMBER(D8),D8> =85%)),10,0),IF(E8="",
IF(OR(D8="E",AND(ISNUMBER(D8),D8> =85%)),5.2,0),IF(AND(ISNUMBER(E8),E8> =85%),IF(OR(D8="E",D8="NA",AND(ISNUMBER(D8),D8> =85%)),10,5.3),
IF(AND(ISNUMBER(E8),E8< 85%),IF(OR(D8="E",AND(ISNUMBER(D8),D8> =85%)),5.4,0))))))

When checking for 85% use AND(ISNUMBER(cell),cell condition)

 

[Special-K99]

Here's another way of doing it, somewhat convoluted.
In a blank cell, let's say G8

in G8
=(D8="E")*1+(D8="NA")*2+(D8="")*4+AND(ISNUMBER(D8),D8> =85%)*8+AND(ISNUMBER(D8),D8< 85%)*16+(E8="E")*32+(E8="NA")*64+(E8="")*128+AND(ISNUMBER(E8),E8> =85%)*256+AND(ISNUMBER(E8),E8< 85%)*512

Each condition when TRUE results in a power of 2.
By summing the results you can tell which of the conditions are TRUE.
So if D8 was E and E8 was E the result would be 1+32 = 33
So we just need to check for the totals of the conditions we are interested in that are TRUE.

in H8
IF(OR(G8=33,G8=34,G8=40,G8=65,G8=66,G8=72,G8=257,G8=258,G8=264),10,IF(OR(G8=48,G8=36,G8=129,G8=136,G8=528,G8=260,G8=513,G8=520),5,0))

 

[excel_sp]

Thank you so much for your quick response.