Formula Audit

excel_sp

New Member
Joined
Feb 25, 2018
Messages
9
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


Value in D8Value in E8Outcome
EE10
NAE10
>=85%E10
<85%E5
E5
ENA10
NANA10
>=85%NA10
<85%NA0
NA0
E5
NA0
>=85%5
<85%0
0
E>=85%10
NA>=85%10
>=85%>=85%10
<85%>=85%5
>=85%5

<tbody>
</tbody>
E<85%5
NA<85%0
>=85%<85%5
<85%<85%0
<85%0

<tbody>
</tbody>

The formula that I am using is as under:
Code:
=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
Value in D8Value in E8Real OutcomeExpected Outcome
NA60%50
NA50

<tbody>
</tbody>


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

Thanks in advance.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
 
Last edited:
Upvote 0
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)
 
Last edited:
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top