| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with If-then statement with multiple conditions
Please help with the below formula
=IF(AND(H30,I30,J30),"Loyal$$$"),IF(AND(H30,I3 0,J3=0),"Retained$$0"),IF(AND(H30,I=0,J30),"Ret urning$0$"),IF(AND(H30,I3=0,J3=0),"New$00"),IF(AN D(H3=0,I30,J30),"Lapsed0$$"),IF(AND(H3=0,I30,J3 =0),"Lapsed0$0"),IF(AND(H3=0,I3=0,J30),"Lost00$") ,IF(AND(H3=0,I3=0,J3=0),"NoSales000"),IF(AND(H3=0, I3=0,J3<0),"ZDead00N"),IF(AND(H3<0,I3<0,J3<0),"ZDe adNNN"),IF(AND(H3<0,I3=0,J3=0),"ZDeadN00"),IF(AND( H3=0,I3<0,J3<0),"ZDead0NN"),IF(AND(H3<0,I3=0,J3<0) ,"ZDeadN0N"),IF(AND(H3=0,I3<0,J3=0),"ZDead0N0"),IF (AND(H3<0,I30,J30),"ZLaspedN$$"),IF(AND(H3<0,I3 0,J3<0),"ZLapsedN$N"),IF(AND(H3<0,I30,J3=0),"ZLap sedN$0"),IF(AND(H3<0,I3<0,J30),"ZLostNN$"),IF(AND (H3<0,I3=0,J30),"ZLostN0$"),IF(AND(H3=0,I3<0,J30 ),"ZLost0N$"),IF(AND(H3=0,I30,J3<0),"ZRecovering0 $N"),IF(AND(H30,I3<0,J30),"ZRecovering$N$"),IF(A ND(H30,I3<0,J3=0),"ZRecovering$N0"),IF(AND(H30,I 3<0,J3<0),"ZRecovering$NN"),IF(AND(H30,I3=0,J3<0) ,"ZRecovering$0N"),IF(AND(H30,I30,J3<0),"ZRetain ed$$N") |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with If-then statement with multiple conditions
Wow, is that a long one.
Not sure what all the trouble is, but you have too many nested IF statements. You can only have 7 IF statements in a row. Now, you can branch them out, so that 7 IF statements follow the first IF's false line of logic, and another 7 IF's follow the true line of logic. Is there any way you can break this down? I'm not sure what all your trying to get, but would a lookup table help? -- Best Regards, Luke M "Julie" wrote: Please help with the below formula =IF(AND(H30,I30,J30),"Loyal$$$"),IF(AND(H30,I3 0,J3=0),"Retained$$0"),IF(AND(H30,I=0,J30),"Ret urning$0$"),IF(AND(H30,I3=0,J3=0),"New$00"),IF(AN D(H3=0,I30,J30),"Lapsed0$$"),IF(AND(H3=0,I30,J3 =0),"Lapsed0$0"),IF(AND(H3=0,I3=0,J30),"Lost00$") ,IF(AND(H3=0,I3=0,J3=0),"NoSales000"),IF(AND(H3=0, I3=0,J3<0),"ZDead00N"),IF(AND(H3<0,I3<0,J3<0),"ZDe adNNN"),IF(AND(H3<0,I3=0,J3=0),"ZDeadN00"),IF(AND( H3=0,I3<0,J3<0),"ZDead0NN"),IF(AND(H3<0,I3=0,J3<0) ,"ZDeadN0N"),IF(AND(H3=0,I3<0,J3=0),"ZDead0N0"),IF (AND(H3<0,I30,J30),"ZLaspedN$$"),IF(AND(H3<0,I3 0,J3<0),"ZLapsedN$N"),IF(AND(H3<0,I30,J3=0),"ZLap sedN$0"),IF(AND(H3<0,I3<0,J30),"ZLostNN$"),IF(AND (H3<0,I3=0,J30),"ZLostN0$"),IF(AND(H3=0,I3<0,J30 ),"ZLost0N$"),IF(AND(H3=0,I30,J3<0),"ZRecovering0 $N"),IF(AND(H30,I3<0,J30),"ZRecovering$N$"),IF(A ND(H30,I3<0,J3=0),"ZRecovering$N0"),IF(AND(H30,I 3<0,J3<0),"ZRecovering$NN"),IF(AND(H30,I3=0,J3<0) ,"ZRecovering$0N"),IF(AND(H30,I30,J3<0),"ZRetain ed$$N") |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with If-then statement with multiple conditions
I think there is a problem with the statement itself...even when I narrow
this down to 7 conditions, I get a #VALUE error. Please help. Julie "Luke M" wrote: Wow, is that a long one. Not sure what all the trouble is, but you have too many nested IF statements. You can only have 7 IF statements in a row. Now, you can branch them out, so that 7 IF statements follow the first IF's false line of logic, and another 7 IF's follow the true line of logic. Is there any way you can break this down? I'm not sure what all your trying to get, but would a lookup table help? -- Best Regards, Luke M "Julie" wrote: Please help with the below formula =IF(AND(H30,I30,J30),"Loyal$$$"),IF(AND(H30,I3 0,J3=0),"Retained$$0"),IF(AND(H30,I=0,J30),"Ret urning$0$"),IF(AND(H30,I3=0,J3=0),"New$00"),IF(AN D(H3=0,I30,J30),"Lapsed0$$"),IF(AND(H3=0,I30,J3 =0),"Lapsed0$0"),IF(AND(H3=0,I3=0,J30),"Lost00$") ,IF(AND(H3=0,I3=0,J3=0),"NoSales000"),IF(AND(H3=0, I3=0,J3<0),"ZDead00N"),IF(AND(H3<0,I3<0,J3<0),"ZDe adNNN"),IF(AND(H3<0,I3=0,J3=0),"ZDeadN00"),IF(AND( H3=0,I3<0,J3<0),"ZDead0NN"),IF(AND(H3<0,I3=0,J3<0) ,"ZDeadN0N"),IF(AND(H3=0,I3<0,J3=0),"ZDead0N0"),IF (AND(H3<0,I30,J30),"ZLaspedN$$"),IF(AND(H3<0,I3 0,J3<0),"ZLapsedN$N"),IF(AND(H3<0,I30,J3=0),"ZLap sedN$0"),IF(AND(H3<0,I3<0,J30),"ZLostNN$"),IF(AND (H3<0,I3=0,J30),"ZLostN0$"),IF(AND(H3=0,I3<0,J30 ),"ZLost0N$"),IF(AND(H3=0,I30,J3<0),"ZRecovering0 $N"),IF(AND(H30,I3<0,J30),"ZRecovering$N$"),IF(A ND(H30,I3<0,J3=0),"ZRecovering$N0"),IF(AND(H30,I 3<0,J3<0),"ZRecovering$NN"),IF(AND(H30,I3=0,J3<0) ,"ZRecovering$0N"),IF(AND(H30,I30,J3<0),"ZRetain ed$$N") |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with If-then statement with multiple conditions
You will have to tell us what you are trying to do. You seem to have many IF
statements, one after another, separated by commas. They aren't nested. For example, the first one from your "formula" is: =IF(AND(H30,I30,J30),"Loyal$$$") That is a formula in itself - two opening brackets, two closing brackets - so it should stop there. You can't just add to it! "Julie" wrote in message ... I think there is a problem with the statement itself...even when I narrow this down to 7 conditions, I get a #VALUE error. Please help. Julie "Luke M" wrote: Wow, is that a long one. Not sure what all the trouble is, but you have too many nested IF statements. You can only have 7 IF statements in a row. Now, you can branch them out, so that 7 IF statements follow the first IF's false line of logic, and another 7 IF's follow the true line of logic. Is there any way you can break this down? I'm not sure what all your trying to get, but would a lookup table help? -- Best Regards, Luke M "Julie" wrote: Please help with the below formula =IF(AND(H30,I30,J30),"Loyal$$$"),IF(AND(H30,I3 0,J3=0),"Retained$$0"),IF(AND(H30,I=0,J30),"Ret urning$0$"),IF(AND(H30,I3=0,J3=0),"New$00"),IF(AN D(H3=0,I30,J30),"Lapsed0$$"),IF(AND(H3=0,I30,J3 =0),"Lapsed0$0"),IF(AND(H3=0,I3=0,J30),"Lost00$") ,IF(AND(H3=0,I3=0,J3=0),"NoSales000"),IF(AND(H3=0, I3=0,J3<0),"ZDead00N"),IF(AND(H3<0,I3<0,J3<0),"ZDe adNNN"),IF(AND(H3<0,I3=0,J3=0),"ZDeadN00"),IF(AND( H3=0,I3<0,J3<0),"ZDead0NN"),IF(AND(H3<0,I3=0,J3<0) ,"ZDeadN0N"),IF(AND(H3=0,I3<0,J3=0),"ZDead0N0"),IF (AND(H3<0,I30,J30),"ZLaspedN$$"),IF(AND(H3<0,I3 0,J3<0),"ZLapsedN$N"),IF(AND(H3<0,I30,J3=0),"ZLap sedN$0"),IF(AND(H3<0,I3<0,J30),"ZLostNN$"),IF(AND (H3<0,I3=0,J30),"ZLostN0$"),IF(AND(H3=0,I3<0,J30 ),"ZLost0N$"),IF(AND(H3=0,I30,J3<0),"ZRecovering0 $N"),IF(AND(H30,I3<0,J30),"ZRecovering$N$"),IF(A ND(H30,I3<0,J3=0),"ZRecovering$N0"),IF(AND(H30,I 3<0,J3<0),"ZRecovering$NN"),IF(AND(H30,I3=0,J3<0) ,"ZRecovering$0N"),IF(AND(H30,I30,J3<0),"ZRetain ed$$N") |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with If-then statement with multiple conditions
Ah ha! In all your statements, after the true value, you have parenthesis.
=IF(AND(H30,I30,J30),"Loyal$$$"),IF(AND... This is closing your IF statement, which you don't want. Should be: =IF(AND(H30,I30,J30),"Loyal$$$",IF(AND.... Also, at least one of your I3 references is missing the number, creating invalid callout. -- Best Regards, Luke M "Julie" wrote: I think there is a problem with the statement itself...even when I narrow this down to 7 conditions, I get a #VALUE error. Please help. Julie "Luke M" wrote: Wow, is that a long one. Not sure what all the trouble is, but you have too many nested IF statements. You can only have 7 IF statements in a row. Now, you can branch them out, so that 7 IF statements follow the first IF's false line of logic, and another 7 IF's follow the true line of logic. Is there any way you can break this down? I'm not sure what all your trying to get, but would a lookup table help? -- Best Regards, Luke M "Julie" wrote: Please help with the below formula =IF(AND(H30,I30,J30),"Loyal$$$"),IF(AND(H30,I3 0,J3=0),"Retained$$0"),IF(AND(H30,I=0,J30),"Ret urning$0$"),IF(AND(H30,I3=0,J3=0),"New$00"),IF(AN D(H3=0,I30,J30),"Lapsed0$$"),IF(AND(H3=0,I30,J3 =0),"Lapsed0$0"),IF(AND(H3=0,I3=0,J30),"Lost00$") ,IF(AND(H3=0,I3=0,J3=0),"NoSales000"),IF(AND(H3=0, I3=0,J3<0),"ZDead00N"),IF(AND(H3<0,I3<0,J3<0),"ZDe adNNN"),IF(AND(H3<0,I3=0,J3=0),"ZDeadN00"),IF(AND( H3=0,I3<0,J3<0),"ZDead0NN"),IF(AND(H3<0,I3=0,J3<0) ,"ZDeadN0N"),IF(AND(H3=0,I3<0,J3=0),"ZDead0N0"),IF (AND(H3<0,I30,J30),"ZLaspedN$$"),IF(AND(H3<0,I3 0,J3<0),"ZLapsedN$N"),IF(AND(H3<0,I30,J3=0),"ZLap sedN$0"),IF(AND(H3<0,I3<0,J30),"ZLostNN$"),IF(AND (H3<0,I3=0,J30),"ZLostN0$"),IF(AND(H3=0,I3<0,J30 ),"ZLost0N$"),IF(AND(H3=0,I30,J3<0),"ZRecovering0 $N"),IF(AND(H30,I3<0,J30),"ZRecovering$N$"),IF(A ND(H30,I3<0,J3=0),"ZRecovering$N0"),IF(AND(H30,I 3<0,J3<0),"ZRecovering$NN"),IF(AND(H30,I3=0,J3<0) ,"ZRecovering$0N"),IF(AND(H30,I30,J3<0),"ZRetain ed$$N") |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need help with If-then statement with multiple conditions
Julie,
You would do well to build a truth table, with the answers included in the table. The macro below will create the truth table in columns L,M,N, and O , from rows 1 to 27, and will put a formula in cell K3 that will replace your formula. You will need to change the values in column O from ZStringOption01 etc. to your desired values - the truth table is based on TRUE for 0, FALSE for <0, and 0 for 0. HTH, Bernie MS Excel MVP Option Base 1 Option Explicit Sub TryNow() Dim myC As Integer Dim i As Integer Dim j As Integer Dim k As Integer Dim myArr As Variant myC = 1 myArr = Array(True, False, 0) For i = 1 To 3 For j = 1 To 3 For k = 1 To 3 Cells(myC, 12).Value = myArr(i) Cells(myC, 13).Value = myArr(j) Cells(myC, 14).Value = myArr(k) Cells(myC, 15).Value = "ZStringOption" & Format(myC, "00") myC = myC + 1 Next k Next j Next i Range("K3").FormulaR1C1 = _ "=INDEX(R1C15:R27C15,SUMPRODUCT((IF(RC[-3]=0,0," & _ "RC[-3]0)=R1C12:R27C12)*(IF(RC[-2]=0,0,RC[-2]0)" & _ "=R1C13:R27C13)*(IF(RC[-1]=0,0,RC[-1]0)" & _ "=R1C14:R27C14)*ROW(R1C15:R27C15)))" End Sub "Julie" wrote in message ... Please help with the below formula =IF(AND(H30,I30,J30),"Loyal$$$"),IF(AND(H30,I3 0,J3=0),"Retained$$0"),IF(AND(H30,I=0,J30),"Ret urning$0$"),IF(AND(H30,I3=0,J3=0),"New$00"),IF(AN D(H3=0,I30,J30),"Lapsed0$$"),IF(AND(H3=0,I30,J3 =0),"Lapsed0$0"),IF(AND(H3=0,I3=0,J30),"Lost00$") ,IF(AND(H3=0,I3=0,J3=0),"NoSales000"),IF(AND(H3=0, I3=0,J3<0),"ZDead00N"),IF(AND(H3<0,I3<0,J3<0),"ZDe adNNN"),IF(AND(H3<0,I3=0,J3=0),"ZDeadN00"),IF(AND( H3=0,I3<0,J3<0),"ZDead0NN"),IF(AND(H3<0,I3=0,J3<0) ,"ZDeadN0N"),IF(AND(H3=0,I3<0,J3=0),"ZDead0N0"),IF (AND(H3<0,I30,J30),"ZLaspedN$$"),IF(AND(H3<0,I3 0,J3<0),"ZLapsedN$N"),IF(AND(H3<0,I30,J3=0),"ZLap sedN$0"),IF(AND(H3<0,I3<0,J30),"ZLostNN$"),IF(AND (H3<0,I3=0,J30),"ZLostN0$"),IF(AND(H3=0,I3<0,J30 ),"ZLost0N$"),IF(AND(H3=0,I30,J3<0),"ZRecovering0 $N"),IF(AND(H30,I3<0,J30),"ZRecovering$N$"),IF(A ND(H30,I3<0,J3=0),"ZRecovering$N0"),IF(AND(H30,I 3<0,J3<0),"ZRecovering$NN"),IF(AND(H30,I3=0,J3<0) ,"ZRecovering$0N"),IF(AND(H30,I30,J3<0),"ZRetain ed$$N") |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date based IF Statements
maybe you can help solve this one..
=IF(TODAY()=C3,0,NOW()-C3-1) the days open are counted based on a start date manually inputed in C3, a closed date is manually inputed in a separate cell, for instance C4. I am trying to craft my IF statement to stop counting days open based on the closure date. Any suggestions? Thx |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date based IF Statements
Not sure but maybe
=IF(OR(C3=TODAY(),C4=""),0,C4-C3-1) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) <FX wrote in message ... maybe you can help solve this one.. =IF(TODAY()=C3,0,NOW()-C3-1) the days open are counted based on a start date manually inputed in C3, a closed date is manually inputed in a separate cell, for instance C4. I am trying to craft my IF statement to stop counting days open based on the closure date. Any suggestions? Thx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple Conditions for IF statement to evaluate | Excel Worksheet Functions | |||
| Conditions with IF statement | Excel Discussion (Misc queries) | |||
| IF Statement with multiple conditions | Excel Discussion (Misc queries) | |||
| If statement using multiple conditions | Excel Worksheet Functions | |||
| COUNTIF Statement with Multiple Conditions in Different Ranges | Excel Worksheet Functions | |||
Linear Mode
