| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Overlaping conditions....may be
=IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT
APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0)),(I12/(1-I11)),IF(I27=1,(I12/(1-I11))-0.5,IF(I271,0,IF(I28=1,(I12/(1-I11))-0.25,IF(I28=2,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),IF(AND(I27=1,I280),(I12/(1-I11))-(0.5)-((COUNTIF(I75:I85,"N"))*0.03),""))))))) This formuila was working fine before today when the figure in I12 went below 50%. The result was in minus instead of 0. any ideas? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overlaping conditions....may be
Hard to tell without getting into your mind and the program. Best to break out the part that controls that and work with it in chunks. One suggestion: if(countif(i23:k25,"NOT APPLICABLE")<3, "Invalid Entry",etc.... -- Don Guillett Microsoft MVP Excel SalesAid Software "Gaurav" wrote in message ... =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0)),(I12/(1-I11)),IF(I27=1,(I12/(1-I11))-0.5,IF(I271,0,IF(I28=1,(I12/(1-I11))-0.25,IF(I28=2,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),IF(AND(I27=1,I280),(I12/(1-I11))-(0.5)-((COUNTIF(I75:I85,"N"))*0.03),""))))))) This formuila was working fine before today when the figure in I12 went below 50%. The result was in minus instead of 0. any ideas? Thanks in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overlaping conditions....may be
That looks like the solution but not sure how to use it. Never used MAX in
such a scenario. "Sandy Mann" wrote in message ... If you don't want a negative return the would wrapping every calculation involving I12 with a number subtracted in a MAX() function do what you want? Like: ,MAX(,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),0), -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0)),(I12/(1-I11)),IF(I27=1,(I12/(1-I11))-0.5,IF(I271,0,IF(I28=1,(I12/(1-I11))-0.25,IF(I28=2,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),IF(AND(I27=1,I280),(I12/(1-I11))-(0.5)-((COUNTIF(I75:I85,"N"))*0.03),""))))))) This formuila was working fine before today when the figure in I12 went below 50%. The result was in minus instead of 0. any ideas? Thanks in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overlaping conditions....may be
mmmmm......
As written, you run in to the 7 nested function problem if you just add the MAX() functions. However, you have an IF() statement: IF(I27=1,(I12/(1-I11))-0.5 before another IF() statement: IF(AND(I27=1,I280),(I12/(1-I11))-(0.5)-((COUNTIF(I75:I85,"N"))*0.03) If I27 is 1, then the formula can never get to the 2nd IF() so it would be better to reverse them. That then allows you to add the MAX() functions without hitting the 7 nested functions limit: =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0)),I12/(1-I11),IF(AND(I27=1,I280),MAX(I12/(1-I11)-0.5-COUNTIF(I75:I85,"N")*0.03,0),IF(I271,0,IF(I28=1,M AX(I12/(1-I11)-0.25,0),IF(I28=2,MAX(I12/(1-I11)-0.25-(COUNTIF(I75:I85,"N")-1)*0.03,0),IF(I27=1,MAX(I12/(1-I11)-0.5,0),""))))))) I have also removed several brackets, (there is no need to enclose 0.25 in brackets and Excel will perform multiplication and division before addition and subtraction without the need of brackets). Check that I have not altered the logic of your formula. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... That looks like the solution but not sure how to use it. Never used MAX in such a scenario. "Sandy Mann" wrote in message ... If you don't want a negative return the would wrapping every calculation involving I12 with a number subtracted in a MAX() function do what you want? Like: ,MAX(,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),0), -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0)),(I12/(1-I11)),IF(I27=1,(I12/(1-I11))-0.5,IF(I271,0,IF(I28=1,(I12/(1-I11))-0.25,IF(I28=2,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),IF(AND(I27=1,I280),(I12/(1-I11))-(0.5)-((COUNTIF(I75:I85,"N"))*0.03),""))))))) This formuila was working fine before today when the figure in I12 went below 50%. The result was in minus instead of 0. any ideas? Thanks in advance. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overlaping conditions....may be
Hey..so far so good. It seems to be working fine. Thanks a ton.
2 more questions for you. 1. what does MAX function do here? 2. Can we add one more condition? i.e.IF(I300,""). "Sandy Mann" wrote in message ... mmmmm...... As written, you run in to the 7 nested function problem if you just add the MAX() functions. However, you have an IF() statement: IF(I27=1,(I12/(1-I11))-0.5 before another IF() statement: IF(AND(I27=1,I280),(I12/(1-I11))-(0.5)-((COUNTIF(I75:I85,"N"))*0.03) If I27 is 1, then the formula can never get to the 2nd IF() so it would be better to reverse them. That then allows you to add the MAX() functions without hitting the 7 nested functions limit: =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0)),I12/(1-I11),IF(AND(I27=1,I280),MAX(I12/(1-I11)-0.5-COUNTIF(I75:I85,"N")*0.03,0),IF(I271,0,IF(I28=1,M AX(I12/(1-I11)-0.25,0),IF(I28=2,MAX(I12/(1-I11)-0.25-(COUNTIF(I75:I85,"N")-1)*0.03,0),IF(I27=1,MAX(I12/(1-I11)-0.5,0),""))))))) I have also removed several brackets, (there is no need to enclose 0.25 in brackets and Excel will perform multiplication and division before addition and subtraction without the need of brackets). Check that I have not altered the logic of your formula. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... That looks like the solution but not sure how to use it. Never used MAX in such a scenario. "Sandy Mann" wrote in message ... If you don't want a negative return the would wrapping every calculation involving I12 with a number subtracted in a MAX() function do what you want? Like: ,MAX(,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),0), -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0)),(I12/(1-I11)),IF(I27=1,(I12/(1-I11))-0.5,IF(I271,0,IF(I28=1,(I12/(1-I11))-0.25,IF(I28=2,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),IF(AND(I27=1,I280),(I12/(1-I11))-(0.5)-((COUNTIF(I75:I85,"N"))*0.03),""))))))) This formuila was working fine before today when the figure in I12 went below 50%. The result was in minus instead of 0. any ideas? Thanks in advance. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overlaping conditions....may be
1. what does MAX function do here?
It returns the higher value of your calculation and zero so that if the calculation was going to be a minus it would return the higher value which is zero. 2. Can we add one more condition? i.e.IF(I300,""). No, not if you want that test to override the other tests, (ie if I300 then return "" no matter what else), unless you have XL2007 because you run into the 7 nested function rule again. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... Hey..so far so good. It seems to be working fine. Thanks a ton. 2 more questions for you. 1. what does MAX function do here? 2. Can we add one more condition? i.e.IF(I300,""). "Sandy Mann" wrote in message ... mmmmm...... As written, you run in to the 7 nested function problem if you just add the MAX() functions. However, you have an IF() statement: IF(I27=1,(I12/(1-I11))-0.5 before another IF() statement: IF(AND(I27=1,I280),(I12/(1-I11))-(0.5)-((COUNTIF(I75:I85,"N"))*0.03) If I27 is 1, then the formula can never get to the 2nd IF() so it would be better to reverse them. That then allows you to add the MAX() functions without hitting the 7 nested functions limit: =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0)),I12/(1-I11),IF(AND(I27=1,I280),MAX(I12/(1-I11)-0.5-COUNTIF(I75:I85,"N")*0.03,0),IF(I271,0,IF(I28=1,M AX(I12/(1-I11)-0.25,0),IF(I28=2,MAX(I12/(1-I11)-0.25-(COUNTIF(I75:I85,"N")-1)*0.03,0),IF(I27=1,MAX(I12/(1-I11)-0.5,0),""))))))) I have also removed several brackets, (there is no need to enclose 0.25 in brackets and Excel will perform multiplication and division before addition and subtraction without the need of brackets). Check that I have not altered the logic of your formula. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... That looks like the solution but not sure how to use it. Never used MAX in such a scenario. "Sandy Mann" wrote in message ... If you don't want a negative return the would wrapping every calculation involving I12 with a number subtracted in a MAX() function do what you want? Like: ,MAX(,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),0), -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0)),(I12/(1-I11)),IF(I27=1,(I12/(1-I11))-0.5,IF(I271,0,IF(I28=1,(I12/(1-I11))-0.25,IF(I28=2,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),IF(AND(I27=1,I280),(I12/(1-I11))-(0.5)-((COUNTIF(I75:I85,"N"))*0.03),""))))))) This formuila was working fine before today when the figure in I12 went below 50%. The result was in minus instead of 0. any ideas? Thanks in advance. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overlaping conditions....may be
Thanks Sandy.
if I300 then return "" no matter what else....this is what i want. when I30 goes 0, that is actually when i want other calculations to be done. "Sandy Mann" wrote in message ... 1. what does MAX function do here? It returns the higher value of your calculation and zero so that if the calculation was going to be a minus it would return the higher value which is zero. 2. Can we add one more condition? i.e.IF(I300,""). No, not if you want that test to override the other tests, (ie if I300 then return "" no matter what else), unless you have XL2007 because you run into the 7 nested function rule again. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... Hey..so far so good. It seems to be working fine. Thanks a ton. 2 more questions for you. 1. what does MAX function do here? 2. Can we add one more condition? i.e.IF(I300,""). "Sandy Mann" wrote in message ... mmmmm...... As written, you run in to the 7 nested function problem if you just add the MAX() functions. However, you have an IF() statement: IF(I27=1,(I12/(1-I11))-0.5 before another IF() statement: IF(AND(I27=1,I280),(I12/(1-I11))-(0.5)-((COUNTIF(I75:I85,"N"))*0.03) If I27 is 1, then the formula can never get to the 2nd IF() so it would be better to reverse them. That then allows you to add the MAX() functions without hitting the 7 nested functions limit: =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0)),I12/(1-I11),IF(AND(I27=1,I280),MAX(I12/(1-I11)-0.5-COUNTIF(I75:I85,"N")*0.03,0),IF(I271,0,IF(I28=1,M AX(I12/(1-I11)-0.25,0),IF(I28=2,MAX(I12/(1-I11)-0.25-(COUNTIF(I75:I85,"N")-1)*0.03,0),IF(I27=1,MAX(I12/(1-I11)-0.5,0),""))))))) I have also removed several brackets, (there is no need to enclose 0.25 in brackets and Excel will perform multiplication and division before addition and subtraction without the need of brackets). Check that I have not altered the logic of your formula. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... That looks like the solution but not sure how to use it. Never used MAX in such a scenario. "Sandy Mann" wrote in message ... If you don't want a negative return the would wrapping every calculation involving I12 with a number subtracted in a MAX() function do what you want? Like: ,MAX(,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),0), -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0)),(I12/(1-I11)),IF(I27=1,(I12/(1-I11))-0.5,IF(I271,0,IF(I28=1,(I12/(1-I11))-0.25,IF(I28=2,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),IF(AND(I27=1,I280),(I12/(1-I11))-(0.5)-((COUNTIF(I75:I85,"N"))*0.03),""))))))) This formuila was working fine before today when the figure in I12 went below 50%. The result was in minus instead of 0. any ideas? Thanks in advance. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overlaping conditions....may be
Try adding that test in an AND() with the other tests and use the final ""
in the formula: =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE",I30=0),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0),I30=0),I12/(1-I11),IF(AND(I27=1,I280,I30=0),MAX(I12/(1-I11)-0.5-COUNTIF(I75:I85,"N")*0.03,0),IF(AND(I271,I30=0),0 ,IF(AND(I28=1,I30=0),MAX(I12/(1-I11)-0.25,0),IF(AND(I28=2,I30=0),MAX(I12/(1-I11)-0.25-(COUNTIF(I75:I85,"N")-1)*0.03,0),IF(AND(I27=1,I30=0),MAX(I12/(1-I11)-0.5,0),""))))))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... Thanks Sandy. if I300 then return "" no matter what else....this is what i want. when I30 goes 0, that is actually when i want other calculations to be done. "Sandy Mann" wrote in message ... 1. what does MAX function do here? It returns the higher value of your calculation and zero so that if the calculation was going to be a minus it would return the higher value which is zero. 2. Can we add one more condition? i.e.IF(I300,""). No, not if you want that test to override the other tests, (ie if I300 then return "" no matter what else), unless you have XL2007 because you run into the 7 nested function rule again. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... Hey..so far so good. It seems to be working fine. Thanks a ton. 2 more questions for you. 1. what does MAX function do here? 2. Can we add one more condition? i.e.IF(I300,""). "Sandy Mann" wrote in message ... mmmmm...... As written, you run in to the 7 nested function problem if you just add the MAX() functions. However, you have an IF() statement: IF(I27=1,(I12/(1-I11))-0.5 before another IF() statement: IF(AND(I27=1,I280),(I12/(1-I11))-(0.5)-((COUNTIF(I75:I85,"N"))*0.03) If I27 is 1, then the formula can never get to the 2nd IF() so it would be better to reverse them. That then allows you to add the MAX() functions without hitting the 7 nested functions limit: =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0)),I12/(1-I11),IF(AND(I27=1,I280),MAX(I12/(1-I11)-0.5-COUNTIF(I75:I85,"N")*0.03,0),IF(I271,0,IF(I28=1,M AX(I12/(1-I11)-0.25,0),IF(I28=2,MAX(I12/(1-I11)-0.25-(COUNTIF(I75:I85,"N")-1)*0.03,0),IF(I27=1,MAX(I12/(1-I11)-0.5,0),""))))))) I have also removed several brackets, (there is no need to enclose 0.25 in brackets and Excel will perform multiplication and division before addition and subtraction without the need of brackets). Check that I have not altered the logic of your formula. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... That looks like the solution but not sure how to use it. Never used MAX in such a scenario. "Sandy Mann" wrote in message ... If you don't want a negative return the would wrapping every calculation involving I12 with a number subtracted in a MAX() function do what you want? Like: ,MAX(,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),0), -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0)),(I12/(1-I11)),IF(I27=1,(I12/(1-I11))-0.5,IF(I271,0,IF(I28=1,(I12/(1-I11))-0.25,IF(I28=2,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),IF(AND(I27=1,I280),(I12/(1-I11))-(0.5)-((COUNTIF(I75:I85,"N"))*0.03),""))))))) This formuila was working fine before today when the figure in I12 went below 50%. The result was in minus instead of 0. any ideas? Thanks in advance. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Overlaping conditions....may be
You know what Sandy? YOU ROCK!!!!
Why couldnt I think of it? I should kill myself. Thanks a ton bud. "Sandy Mann" wrote in message ... Try adding that test in an AND() with the other tests and use the final "" in the formula: =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE",I30=0),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0),I30=0),I12/(1-I11),IF(AND(I27=1,I280,I30=0),MAX(I12/(1-I11)-0.5-COUNTIF(I75:I85,"N")*0.03,0),IF(AND(I271,I30=0),0 ,IF(AND(I28=1,I30=0),MAX(I12/(1-I11)-0.25,0),IF(AND(I28=2,I30=0),MAX(I12/(1-I11)-0.25-(COUNTIF(I75:I85,"N")-1)*0.03,0),IF(AND(I27=1,I30=0),MAX(I12/(1-I11)-0.5,0),""))))))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... Thanks Sandy. if I300 then return "" no matter what else....this is what i want. when I30 goes 0, that is actually when i want other calculations to be done. "Sandy Mann" wrote in message ... 1. what does MAX function do here? It returns the higher value of your calculation and zero so that if the calculation was going to be a minus it would return the higher value which is zero. 2. Can we add one more condition? i.e.IF(I300,""). No, not if you want that test to override the other tests, (ie if I300 then return "" no matter what else), unless you have XL2007 because you run into the 7 nested function rule again. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... Hey..so far so good. It seems to be working fine. Thanks a ton. 2 more questions for you. 1. what does MAX function do here? 2. Can we add one more condition? i.e.IF(I300,""). "Sandy Mann" wrote in message ... mmmmm...... As written, you run in to the 7 nested function problem if you just add the MAX() functions. However, you have an IF() statement: IF(I27=1,(I12/(1-I11))-0.5 before another IF() statement: IF(AND(I27=1,I280),(I12/(1-I11))-(0.5)-((COUNTIF(I75:I85,"N"))*0.03) If I27 is 1, then the formula can never get to the 2nd IF() so it would be better to reverse them. That then allows you to add the MAX() functions without hitting the 7 nested functions limit: =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0)),I12/(1-I11),IF(AND(I27=1,I280),MAX(I12/(1-I11)-0.5-COUNTIF(I75:I85,"N")*0.03,0),IF(I271,0,IF(I28=1,M AX(I12/(1-I11)-0.25,0),IF(I28=2,MAX(I12/(1-I11)-0.25-(COUNTIF(I75:I85,"N")-1)*0.03,0),IF(I27=1,MAX(I12/(1-I11)-0.5,0),""))))))) I have also removed several brackets, (there is no need to enclose 0.25 in brackets and Excel will perform multiplication and division before addition and subtraction without the need of brackets). Check that I have not altered the logic of your formula. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... That looks like the solution but not sure how to use it. Never used MAX in such a scenario. "Sandy Mann" wrote in message ... If you don't want a negative return the would wrapping every calculation involving I12 with a number subtracted in a MAX() function do what you want? Like: ,MAX(,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),0), -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Gaurav" wrote in message ... =IF(AND(I22="NOT APPLICABLE",I23="NOT APPLICABLE",I24="NOT APPLICABLE",I25="NOT APPLICABLE"),"INVALID ENTRY",IF(AND(OR(I28="NOT APPLICABLE",I28=0),OR(I27="NOT APPLICABLE",I27=0)),(I12/(1-I11)),IF(I27=1,(I12/(1-I11))-0.5,IF(I271,0,IF(I28=1,(I12/(1-I11))-0.25,IF(I28=2,((I12/(1-I11))-(0.25)-((COUNTIF(I75:I85,"N")-1)*0.03)),IF(AND(I27=1,I280),(I12/(1-I11))-(0.5)-((COUNTIF(I75:I85,"N"))*0.03),""))))))) This formuila was working fine before today when the figure in I12 went below 50%. The result was in minus instead of 0. any ideas? Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 2 Conditions + Sum of a colum matching those conditions | Excel Worksheet Functions | |||
| shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
| Need to Sum when THREE Conditions are met | Excel Worksheet Functions | |||
| How to get rid of overlaping cells | Excel Discussion (Misc queries) | |||
| How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions | |||
![Gaurav[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
