Problem with complicated formula

[agarwaldvk]

Can someone help me with this :-

The formula below works fine and may in cases produce a value of 0. I
such cases, I would like to display value to read "Not Relevant".

Could too many levels of nesting be the problem here?

=IF(ISERROR(SUMIF(INDEX(JAndLPolicies_SA,,1),"New t
Fund",INDEX(JAndLPolicies_SA,,(MATCH(YEAR(EDATE($B$1,0)) & "/"
IF(MONTH(EDATE($B$1,0))<10,"0"
MONTH(EDATE($B$1,0)),MONTH(EDATE($B$1,0))),'JoinsAndLapses
Policies'!$A$5:$N$5,0)+1)))*12/($B$21-$C$21)),"No
Relevant",SUMIF(INDEX(JAndLPolicies_SA,,1),"New t
Fund",INDEX(JAndLPolicies_SA,,(MATCH(YEAR(EDATE($B$1,0)) & "/"
IF(MONTH(EDATE($B$1,0))<10,"0"
MONTH(EDATE($B$1,0)),MONTH(EDATE($B$1,0))),'JoinsAndLapses
Policies'!$A$5:$N$5,0)+1)))*12/($B$21-$C$21))

I tried to introduce an OR function after the IF and before the ISERRO
function as shown but it always came up with an error saying that th
formula contains an error when there was no error in the formula.


=IF(or (SUMIF(INDEX(JAndLPolicies_SA,,1),"New t
Fund",INDEX(JAndLPolicies_SA,,(MATCH(YEAR(EDATE($B$1,0)) & "/"
IF(MONTH(EDATE($B$1,0))<10,"0"
MONTH(EDATE($B$1,0)),MONTH(EDATE($B$1,0))),'JoinsAndLapses
Policies'!$A$5:$N$5,0)+1)))*12/($B$21-$C$21)=0,ISERROR(SUMIF(INDEX(JAndLPolicies_SA,,1),"Ne
to Fund",INDEX(JAndLPolicies_SA,,(MATCH(YEAR(EDATE($B$1,0)) & "/"
IF(MONTH(EDATE($B$1,0))<10,"0"
MONTH(EDATE($B$1,0)),MONTH(EDATE($B$1,0))),'JoinsAndLapses
Policies'!$A$5:$N$5,0)+1)))*12/($B$21-$C$21))),"No
Relevant",SUMIF(INDEX(JAndLPolicies_SA,,1),"New t
Fund",INDEX(JAndLPolicies_SA,,(MATCH(YEAR(EDATE($B$1,0)) & "/"
IF(MONTH(EDATE($B$1,0))<10,"0"
MONTH(EDATE($B$1,0)),MONTH(EDATE($B$1,0))),'JoinsAndLapses
Policies'!$A$5:$N$5,0)+1)))*12/($B$21-$C$21))


I also tried to remove some nesting by trying to get the month numbe
from a date in a double digit format viz "dd". The text functio
text(month(b1),"mm") also returned 1 instead of 07 when cell b
contains the date 01/07/2003.

Any suggestions?


Best regards and Thanks!



Deepak Agarwa

 

[Jerry W. Lewis]

If you had too many nesting levels, Excel would complain when you
entered the formula.

Your formula (simplified) is
=IF(ISERROR(formula1),"Not Relevant",formula1)
You can only get 0 if formula1 returns 0. Your formula1 is
SUMIF(args)*12/($B$21-$C$21))
so formula1=0 only if SUMIF(args)=0

Add an extra condition to your formula
=IF(ISERROR(formula1)+(SUMIF(args)=0),"Not Relevant",formula1)
Here the "+" functions as a logical OR.

Jerry

 

[agarwaldvk]

Jerry

Makes sense. I shall try that tomorrow at work. I am sure that shoul
work.

By the way, would you mind having a look at my latest query unde
"Programming" as well? That would be a boon!

Thanks a lot!

Best regards


Deepa