Apr 22 2019 08:43 AM
Hello, Im having problems getting the right syntax for nexting IF statements.
Basically Im comparing values already placed into cells G5, H5 and I5 to return a value placed into cell E5.
This is the logic Im attempting to create and embed into one cell.
=IF (G5="LOW" and H5="LOW"), then IF (I5="LOW", "T", IF(I5="HIGH", "T", "T"))
=IF (G5="LOW" and H5="HIGH") then IF (I5="LOW", "T", IF(I5=HIGH, "NT", "NT"))
=IF (G5="MODERATE" and H5=LOW) then (I5=LOW, "T", IF(I5="HIGH", "NT", "NT"))
=IF (G5="MODERATE") and H5="HIGH" then (I5="LOW", "OT", IF(I5="HIGH", "T", "OT"))
=IF (G5="HIGH" and H5="LOW") then (I5=LOW, "T", IF(I5=HIGH, "NT", "T"))
=IF (G5="HIGH" and H5="HIGH") then IF (I5="LOW", "OT", IF(I5="HIGH", "T", "OT"))
If anyone can assist, much appreciated...
Apr 22 2019 03:23 PM
I agree. I believe I was referring to the formula version that you presented that is free from CSE. ;)
Cheers!
Apr 22 2019 03:58 PM
I stepped away from my desk for a while. Such wonderful responses. Thank you all!
I think I am making headway.
I am placing:
=IF(G5="LOW",IF(H5="LOW","T",IF(G5="LOW",IF(H5="HIGH", IF(I5="LOW","T","NT"))))), IF(G5="MODERATE",IF(H5="LOW",IF(I5="LOW","T","NT"))), IF(G5="MODERATE",IF(H5="HIGH",IF(I5="HIGH","T","OT"))), IF(G5="HIGH",IF(H5="LOW",IF(I5="HIGH","NT","T"))), IF(G5="HIGH",IF(H5="HIGH",IF(I5="HIGH","T","OT")))
in the E5 cell to attempt to accommodate the "moderate" entries that could appear in the I5 column. However I am getting a #VALUE! error.
I think my syntax of too many IF statements perhaps? or is there a limit on how many you can place in one formula?
thank you
dan
Apr 22 2019 04:00 PM
thank you Celia,
I haven't worked with arrays before, Ill see if I can implement them in somehow.
Ill have to think on this one for a bit.
thank you for all the wonderful help!
Apr 22 2019 05:21 PM
thank you Celia! This worked great! I was able to populate what I needed and used the CSE to enter in the formulas!
thanks again to everyone for all their help!!
Apr 22 2019 07:24 PM
Apr 23 2019 01:51 AM
Hi Dan,
If format a bit your formula it'll be
=IF(G5="LOW", IF(H5="LOW","T", IF(G5="LOW", IF(H5="HIGH", IF(I5="LOW","T","NT"))))), IF(G5="MODERATE",IF(H5="LOW",IF(I5="LOW","T","NT"))), IF(G5="MODERATE",IF(H5="HIGH",IF(I5="HIGH","T","OT"))), IF(G5="HIGH",IF(H5="LOW",IF(I5="HIGH","NT","T"))), IF(G5="HIGH",IF(H5="HIGH",IF(I5="HIGH","T","OT")))
with brackets in red you close it, all after the comma gives incorrect syntax
More correct will be
=IF(G5="LOW", IF(H5="LOW", "T", IF(I5="LOW","T","NT")), IF(G5="MODERATE", IF(H5="LOW", IF(I5="LOW","T","NT"), IF(I5="HIGH","T","OT")), IF(H5="LOW", IF(I5="HIGH","NT","T"), IF(I5="HIGH","T","OT")) ))
and in attached file
Apr 23 2019 06:41 AM
Thank you Sergei!
This works a treat!!! I am new to excel formulas, and I have learned so much.
Thanks again!!
Apr 23 2019 11:14 AM