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.