Forum Discussion
Nested IF formula in Excel 365
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!
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?
You can think of this has a flow chart type of analysis. G5, H5 and I5 are prepopulated with HIGH, MODERATE, LOW.
The logic I'm attempting is a flowchart type layout:
IF G5 LOW AND H5 LOW then Look in cell I5 for value and place the "T", "NT", or OT" in E5
In this case G5 and H5 are LOW and use the following formula to pick which (T, OT, NT) to place in E5
IF (I5="LOW", "T", IF(I5="HIGH", "T", "T"))
Since LOW is in cell I5 , it results in "T" in E5.
Depending on values in G5 and H5...(Low, Low or Moderate,High or High,High...ect) it changes how the Value in I5 is interpreted to place the T, OT, or NT in E5. Sometimes it will seem redundant, but the type of training will cover multiple scenarios.
I hope Im explaining it correctly.
thank you
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!
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!!
DanGreene, unfortunately, the information you provide is not enough.
You cannot say "Depending on values in G5 and H5...(Low, Low or Moderate,High or High,High...ect) it changes how the Value in I5 is interpreted to place the T, OT, or NT in E5." and expect Excel to know how you are expecting it to interpret the results. The person building the spreadsheet need to teach Excel how to calculate the final result depending on each possible scenario for F5, G5 and H5.
If you follow SergeiBaklan 's suggestion it will help us find a solution for you. If you prefer, you can also use the structure below to fill in the expected final result for each case.
Dan, perhaps it's easier to fill these simple matrices to explain desired result
(in attached file)
Sorry, I didn't see this when I posted; I had been away from the computer.
