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
<tbody>
</tbody>
<tbody>
</tbody>
The formula that I am using is as under:
With above code, most of the time, the outcome is expected, except in below two situations
<tbody>
</tbody>
I am not able to find the error in the formula. Can you please guide me?
Thanks in advance.
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 D8 | Value in E8 | Outcome |
E | E | 10 |
NA | E | 10 |
>=85% | E | 10 |
<85% | E | 5 |
E | 5 | |
E | NA | 10 |
NA | NA | 10 |
>=85% | NA | 10 |
<85% | NA | 0 |
NA | 0 | |
E | 5 | |
NA | 0 | |
>=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 D8 | Value in E8 | Real Outcome | Expected Outcome |
NA | 60% | 5 | 0 |
NA | 5 | 0 |
<tbody>
</tbody>
I am not able to find the error in the formula. Can you please guide me?
Thanks in advance.