Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How many IF functions can you have?
I have the following formula:
=IF(AND(S32="Yes",U32="Yes",W32="Yes",Y32="Yes"),Z 32+AA32+AB32+AC32,IF(U32="",Z32,IF(U32="Yes",Z32+A A32,IF(W32="",Z32+AA32,IF(W32="Yes",Z32+AA32+AB32, IF(Y32="",Z32+AA32+AB32,IF(Y32="Yes",Z32+AA32+AB32 +AC32,IF(M32="No",0)))))))) The problem is when W32 is "Yes". It does not add the totals in Z32,AA32, & AB32. However, when S32, U32, W32, & Y32 = "Yes", then the total of all four cells totals the correct total. There probably is an easier formula. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How many IF functions can you have?
The Excel limit for nested IF is 7. Looks like you're right at the limit, so
you'll be ok as long as you don't add another IF. Nothing happens when your 5th IF (W32="Yes") is true, because you have already covered all the possibilities by the 3rd IF (U32="Yes"). So basically no matter what the combination of your 4 cells are, it will not go any further than the 3rd IF. Don't really know how to correct the formula because I don't know what's the exact conditions you want. Maybe if you write it out in words I can figure out the formula to use. -Simon "heater" wrote: I have the following formula: =IF(AND(S32="Yes",U32="Yes",W32="Yes",Y32="Yes"),Z 32+AA32+AB32+AC32,IF(U32="",Z32,IF(U32="Yes",Z32+A A32,IF(W32="",Z32+AA32,IF(W32="Yes",Z32+AA32+AB32, IF(Y32="",Z32+AA32+AB32,IF(Y32="Yes",Z32+AA32+AB32 +AC32,IF(M32="No",0)))))))) The problem is when W32 is "Yes". It does not add the totals in Z32,AA32, & AB32. However, when S32, U32, W32, & Y32 = "Yes", then the total of all four cells totals the correct total. There probably is an easier formula. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How many IF functions can you have?
You can next function (including IF) to 7 levels only
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "heater" wrote in message ... I have the following formula: =IF(AND(S32="Yes",U32="Yes",W32="Yes",Y32="Yes"),Z 32+AA32+AB32+AC32,IF(U32="",Z32,IF(U32="Yes",Z32+A A32,IF(W32="",Z32+AA32,IF(W32="Yes",Z32+AA32+AB32, IF(Y32="",Z32+AA32+AB32,IF(Y32="Yes",Z32+AA32+AB32 +AC32,IF(M32="No",0)))))))) The problem is when W32 is "Yes". It does not add the totals in Z32,AA32, & AB32. However, when S32, U32, W32, & Y32 = "Yes", then the total of all four cells totals the correct total. There probably is an easier formula. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How many IF functions can you have?
I came up with a different solution. I was making it way to complicated -
Thanks! "SimonCC" wrote: The Excel limit for nested IF is 7. Looks like you're right at the limit, so you'll be ok as long as you don't add another IF. Nothing happens when your 5th IF (W32="Yes") is true, because you have already covered all the possibilities by the 3rd IF (U32="Yes"). So basically no matter what the combination of your 4 cells are, it will not go any further than the 3rd IF. Don't really know how to correct the formula because I don't know what's the exact conditions you want. Maybe if you write it out in words I can figure out the formula to use. -Simon "heater" wrote: I have the following formula: =IF(AND(S32="Yes",U32="Yes",W32="Yes",Y32="Yes"),Z 32+AA32+AB32+AC32,IF(U32="",Z32,IF(U32="Yes",Z32+A A32,IF(W32="",Z32+AA32,IF(W32="Yes",Z32+AA32+AB32, IF(Y32="",Z32+AA32+AB32,IF(Y32="Yes",Z32+AA32+AB32 +AC32,IF(M32="No",0)))))))) The problem is when W32 is "Yes". It does not add the totals in Z32,AA32, & AB32. However, when S32, U32, W32, & Y32 = "Yes", then the total of all four cells totals the correct total. There probably is an easier formula. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How many IF functions can you have?
First of all you need to understand how excel makes it's calculations. In your formula you are telling excel to total cells Z, AA, AB, & AC 32 if cells S, U, W, & Y32 =YES. It does this correctly. In your next if statement (next condition) you tell excel to total cell C32 if cell U32 is blank. It does this correctly because condition 1 is now no longer true. In you next condition you telling excel to contidict it's self by saying IF cell U32 equals YES, then total cells Z and AA 32. By putting Yes back in cell U32 unless I'm missing something now tell excel to return to the first condition which should total 13 the sum of cells Z, AA, AB, & AC 32. You can have more than 7 nested if's however, you need to use =CONCATENATE. This will give you 30 conditions to use in your formula. There is also a way to bypass the 30 conditions but thats another story. Try to be a little more specific as to what you want the outcome to be. Hope this helps. Ed -- patele ------------------------------------------------------------------------ patele's Profile: http://www.excelforum.com/member.php...o&userid=35849 View this thread: http://www.excelforum.com/showthread...hreadid=566183 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I get Excel to determine the line curve formula without graph. | Excel Discussion (Misc queries) | |||
User-defined functions created in Excel 2000 fail in Excel 2003 | Excel Discussion (Misc queries) | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Visible rows and functions that work | Excel Worksheet Functions | |||
Improvements for text finding functions | Excel Discussion (Misc queries) |