Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- 470K Members
- 5,594 Online
- 568K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Nested IF formula in Excel 365

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
## Nested IF formula in Excel 365

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-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...

Labels:

- Tags:
- Nested IF statements

27 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 08:54 AM

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.

- Tags:
- @Celia_Alves

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 09:01 AM

@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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 09:09 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 09:15 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 09:22 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 09:25 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 09:49 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 09:51 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 10:02 AM

Dan, perhaps it's easier to fill these simple matrices to explain desired result

(in attached file)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 10:14 AM

@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 @Sergei Baklan '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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 10:17 AM

Hello,

Attached is my spread sheet. Hopefully this will help.

Im looking at using 6 logic formulas nested in cell E5 to process the value found I5 to route what formula to use to determine what value to place into E5. T, NT, OT.

=IF (G5="LOW" and H5="LOW"), THEN IF (I5="LOW", "T", IF(I5="HIGH", "T", "T")) ELSE

=IF (G5="LOW" and H5="HIGH") THEN IF (I5="LOW", "T", IF(I5=HIGH, "NT", "NT")) ELSE

=IF (G5="MODERATE" and H5=LOW) THEN (I5=LOW, "T", IF(I5="HIGH", "NT", "NT")) ELSE

=IF (G5="MODERATE" and H5="HIGH") THEN (I5="LOW", "OT", IF(I5="HIGH", "T", "OT")) ELSE

=IF (G5="HIGH" and H5="LOW") THEN (I5=LOW, "T", IF(I5=HIGH, "NT", "T")) ELSE

=IF (G5="HIGH" and H5="HIGH") THEN IF (I5="LOW", "OT", IF(I5="HIGH", "T", "OT"))

thank you

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 10:38 AM - edited 04-22-2019 12:19 PM

Solutionplease 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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 10:59 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 11:21 AM

By the way, to simplify the maintenance you may avoid CSE transforming the formula to

=INDEX($H$13:$H$30,MATCH(1,INDEX(($E$13:$E$30=$F6)*($F$13:$F$30=$G6)*($G$13:$G$30=$H6),0),0))

And if the matrix is correct, equivalent nested IF could be

=IF(F6="LOW", IF(G6="LOW", "T", IF(H6="LOW","T","NT")), IF(F6="MODERATE", IF(G6="LOW", IF(H6="LOW","T","NT"), IF(H6="HIGH","T","OT")), IF(G6="LOW", IF(H6="HIGH","NT","T"), IF(H6="HIGH","T","OT")) ))

or so

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 12:02 PM

A different strategy that uses table lookups to determine the training category.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 12:05 PM

Sorry, I didn't see this when I posted; I had been away from the computer.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 12:22 PM - edited 04-22-2019 12:25 PM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 12:23 PM

That's right, @Sergei Baklan! That's a very nice one too. Thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 02:11 PM

@Celia_Alves , thank you, but nested IF here is only to demonsrate how it looks like. It has no sence in this case - formulas which transform score numbers into text levels are under question. Even if assume they are correct this logic could be changed from time to time. Nested IF with hardcoded conditions is not the a good solution from maintenance point of view.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-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!!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-22-2019 07:24 PM

Glad to hear you found a solution that works for you, @DanGreene! Happy that I could help. There were other valid solutions in this thread also.

Good luck with your project. :-)

Good luck with your project. :-)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-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!!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

04-23-2019 11:14 AM

Related Conversations

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
36.3K
Views

7 Likes

35 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
185K
Views

8 Likes

30 Replies

flashing a white screen while open new tab

Deleted
in
Discussions
on
10-05-2019
31.6K
Views

14 Likes

14 Replies

Security Community Webinars

Valon_Kolica
in
Security, Privacy & Compliance
on
10-22-2019
17.4K
Views

12 Likes

13 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft