Forum Discussion
Nested IF formula in Excel 365
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...
please see screenshot and file attached.
The formula that I used is an array formula. To make the formula to work, you need to do CTRL+SHIFT+ENTER after you finish writing it or after you edit it in the formula bar. The formula will get curly brackets at the beginning and ending after you hit those three keys.
Good luck!
27 Replies
A different strategy that uses table lookups to determine the training category.
That's the type of information we needed. I believe DanGreene now has a few solutions that can be implemented according to preference. Good luck!
DanGreene , by the way, expressions like
IF(I5="HIGH", "T", "T")
have no sense, it always returns "T" independently on what do you have in I5
Sergei,
Looking at the example:
=IF (G5="LOW" and H5="LOW"), then IF (I5="LOW", "T", IF(I5="HIGH", "T", "T"))
I have IF(I5="HIGH", "T", "T")) listed this way because there is a third value of "Moderate" that could be in the Cell I5 which should return a value of "T" as well.
thank you
dan
Dan, sorry, but I didn't catch. The expression in second part
=IF(I5="LOW","T",IF(I5="HIGH","T","T"))
is equal to
= "T"
will it be in I5 "MODERATE", "LOW" or any other value
Sergei,
thank you for the reply. Yes this is true. In my analysis of data I collected thru employee surveys, I can distill the data given back into either T, OT or NT to populate the cell E5. Depending on what values are placed into G5, H5, or I5, the outcome may indeed show the value of "T" for both. These are types of training that my department will need to implement according to those values.
I see your point though, I could rewrite the formula on those that have the same "training" value to simplify.
- Succinctly, what conditions must be fulfilled for the required formula to return each of the possible results? If you cannot answer this question, I am sorry to inform you that you cannot also expect an answer.
Hi! It seems that your final result can be T, NT, OT.
Can you please explain on your words (not formulas) the conditions to apply each one of the results?
Please tag me so that I can come back to this thread.
Hello! Thanks for replying. Yes I am taking survey results and distilling down the results in 3 categories:
Difficulty, Importance, and Frequency. I will apply the logic of the excel formula to return a value of T, NT or OT for levels of training needed for my department.
Difficulty has 3 levels, (low, moderate, high), Importance has 2 levels (low, high) and Frequency has 3 levels (low, moderate, high)
The values are assigned from other calculations from survey results <2.5=low, 2.6-3.4=moderate, >=3.5=high.
So in Cell E5, I need to compare values in G5,H5 and I5 using the logic formulas shown to ultimately provide a value of T, OT, or NT .
Hopefully this makes sense! thank you.
it is still not clear when you want T, NT or NT to appear.
In your words:
- what needs to happen with the values of G5, H5 and I5 to make E5 = T?
- what needs to happen with the values of G5, H5 and I5 to make E5 = NT?
- what needs to happen with the values of G5, H5 and I5 to make E5 = NT?
