Hi Jordan. I am an Excel user like you.
How to add an imbedded IF function inside another IF function depends on what kind of conditions you have and what result you want. Here are two basic illustrations of how to put them together.
- =IF(A1="RED","Christmas",IF(A1="Green","Easter","None"))
In this formula the first Logical Test is A1="Red". The true result is "Christmas". So, if A1 does have "Red" the result will be "Christmas". The imbedded IF function is in the Value if False position. If A1 does not have "Red" the second IF function comes into play as the Value if False option and if A1 has "Green" then the result will be "Easter" as the true result of the second IF function. If A1 does not have "Green" then the result will be "None" as the False result of the second IF function.
- =IF(A1="Red",IF(B1="Green","Christmas","Valentine's"),"None")
In this formula the second IF function is in the Value if True position so if A1 has "Red" it will go on to the Value if True IF function to check if B1 has "Green". If this is true (we have met both Logical Tests now) then the result will be the true result of the second IF function "Christmas". If the Logical Test is false and B1 doesn't have "Green", then we have met the first test but not the second so the result will be the false result of the second IF function "Valentine's". If on the other hand the first Logical Test is false and A1 does not have "Red" then the formula will jump over the Value if True IF function and go to the Value if False part of the first IF function that is after the second, completed, imbedded IF function and the result will be "None".
Hopefully this will help on how these are constructed. If you need more specific information you will need to include some basic details about your data and layout. Be sure to remove sensitive information.
Answer from Rich~M on learn.microsoft.comStringing Multiple IF functions together.
Excel Nested IF statement with various text conditions - Stack Overflow
IF Then Formula with Multiple Outcomes
Multiple IF Statement Returning Different Text
Videos
Hi Jordan. I am an Excel user like you.
How to add an imbedded IF function inside another IF function depends on what kind of conditions you have and what result you want. Here are two basic illustrations of how to put them together.
- =IF(A1="RED","Christmas",IF(A1="Green","Easter","None"))
In this formula the first Logical Test is A1="Red". The true result is "Christmas". So, if A1 does have "Red" the result will be "Christmas". The imbedded IF function is in the Value if False position. If A1 does not have "Red" the second IF function comes into play as the Value if False option and if A1 has "Green" then the result will be "Easter" as the true result of the second IF function. If A1 does not have "Green" then the result will be "None" as the False result of the second IF function.
- =IF(A1="Red",IF(B1="Green","Christmas","Valentine's"),"None")
In this formula the second IF function is in the Value if True position so if A1 has "Red" it will go on to the Value if True IF function to check if B1 has "Green". If this is true (we have met both Logical Tests now) then the result will be the true result of the second IF function "Christmas". If the Logical Test is false and B1 doesn't have "Green", then we have met the first test but not the second so the result will be the false result of the second IF function "Valentine's". If on the other hand the first Logical Test is false and A1 does not have "Red" then the formula will jump over the Value if True IF function and go to the Value if False part of the first IF function that is after the second, completed, imbedded IF function and the result will be "None".
Hopefully this will help on how these are constructed. If you need more specific information you will need to include some basic details about your data and layout. Be sure to remove sensitive information.
Hi Jordan.
You could check out the SWITCH function that peiyezhu referred to, or I believe that the IFS function may be more applicable to your data. If I read your data correctly, you have unique conditions for each of the five results you want. With the IFS function you can enter each of those conditions followed by the result you want for that particular set of conditions.
Here is an example of how the formula works.
=IFNA(IFS(AND(A1=1,A2=2),"Blue",AND(A1=1,A2=3),"Green",AND(A1=1,A2=4),"Red"),"")
.
Inside the IFS function I have included three AND functions as the condition statements and three results, one for each of the condition statements. The first A1=1 and A2=2 is false so there is no result. The second A1=1 and A2=3 is true so the result that is matched with that statement is Green. This works best if each of the conditions is mutually exclusive from the others, but you can have them set in an order because Excel will stop at the first true result.
You can enter your AND, OR, or AND/OR statements in the Logical Test positions with the appropriate result following each. I also wrapped this in an IFNA function because if none of the conditions is found to be true the result will be #N/A. By using the IFNA function you can control what the result will be in that case. The formula is set to return a blank cell with the two quotation marks at the end of the formula. If you want it to say something else just enter that value inside those quotation marks at the end of the formula.
Please try:
=IF(H5="CIVIL",5000,IF(OR(H5="ARCHITECT",H5="ELECTRIC"),3000,2000))*I5
There will be more professions with more values, so a "single formula" approach is second best. You should use a table with profession and value, and use VLOOKUP, then you can change values and add professions as you like without updating spaghetti formula code ... e.g.
A B
1 Civil 5000
2 Arch 3000
3 Elec 3000
4 Mech 2000
Specifying a profession in H5, you would use the following formula in I5:
=VLOOKUP(H5,A1:B4,2,FALSE)
Looking at your edit ... one more reason NOT to hide the unit cost in a formula, because in 2016 the rates might be different --> use a table!
Hello! I am trying to set up an IF formula where there are multiple outcomes. Specifcally, if it is above 90.01 then it gets a 5, above 85.01 then if will get a 4, etc.
I first tried it this way which is what I the tutorial I looked at had their formula set up like:
But I got the error message:
But it is my understanding you can have over 50 functions of this (according to the tutorial)
Then I tried it like this:
But got the #Value! error. I have tried closing off the formula in various ways but just can't make this formula work for me. Any tips would be greatly appreciated! :)