Overlaping conditions....may be

  • Thread starter Thread starter Gaurav
  • Start date Start date
G

Gaurav

=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(I27>1,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,I28>0),(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.
 
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....
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
That looks like the solution but not sure how to use it. Never used MAX in
such a scenario.
 
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,I28>0),(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,I28>0),MAX(I12/(1-I11)-0.5-COUNTIF(I75:I85,"N")*0.03,0),IF(I27>1,0,IF(I28=1,MAX(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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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(I30>0,"").
 
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(I30>0,"").
Click to expand...

No, not if you want that test to override the other tests, (ie if I30>0 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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Thanks Sandy.

if I30>0 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.
 
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,I28>0,I30=0),MAX(I12/(1-I11)-0.5-COUNTIF(I75:I85,"N")*0.03,0),IF(AND(I27>1,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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
You know what Sandy? YOU ROCK!!!!

Why couldnt I think of it? I should kill myself.

Thanks a ton bud.
 
Back
Top