I Need Help in My Spreadsheet Formula

[cyliyu]

Dear All,

I have a formula in column E and need help. The result will be based on the calculated result in column H.
- if column H6 is empty - column E6 will be blank.
- if column H6 = DIT1 or DIT2 - column E6 display OPU
- if column H6 = MIP1 or MIP2 or N.A - column E6 display PPU
Can my formula be simplified to meet the conditions?

Thanks.

 

[Hui]

E6: =IF(H6="","",IF(OR(H6="DIT1",H6="DIT2"),"OPU",IF(OR(H6="MIP1",H6="MIP2",H6="N.A"),"PPU","")))
Copy down

But this excludes the case where they can equal MII1 & MII2 which are in the existing formula

 

[cyliyu]

Thanks Hui.

Yes, I missed out the MII1 and MII2 which are suppose to be part of the condition.
- if column H6 is empty - column E6 will be blank.
- if column H6 = DIT1 or DIT2 or N.A - column E6 display OPU
- if coulum H6 =MII1 or MII2 - column E6 display PPU
- if column H6 = MIP1 or MIP2 - column E6 display PPU.

 

[cyliyu]

=IF(J6="","",IF(OR(J6="DIT1",J6="DIT2"),"OPU",IF(OR(J6="MIP1",J6="MIP2"),"PPU",IF(OR(J6="MII1",J6="MII2"),"PPU",IF(J6="N.A"),"OPU",""))))

I can't have this formula works if J6="N.A". it will display as "blank".
Need help.

 

[Hui]

Do you mean N.A or the #N/A error message?

 

[Hui]

You should always put all the same results conditions together

=IF(J6="","",IF(OR(J6="DIT1",J6="DIT2",J6="N.A"),"OPU",IF(OR(J6="MIP1",J6="MIP2",J6="MII1",J6="MII2"),"PPU","")))

 

[NARAYANK991]

Hi ,

In case you are interested in reducing the length of the formula , you can also use :

=IF(J6="","",IF(OR(J6={"DIT1","DIT2","N.A"}),"OPU",IF(OR(J6={"MIP1","MIP2","MII1","MII2"}),"PPU","")))

Narayan

 

[cyliyu]

Do you mean N.A or the #N/A error message?

I want the result to display as "OPU" when N.A is obtained in "Plate No."
But it displayed as "Blank".

I tried yours and Narayan's formula, both having the same results.

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[cyliyu]

Hi ,

See the attached file.

Narayan

Thanks Narayan.
I noticed you make changes to Column "H" to make it work.