Nesting Eroor - more than 64

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jaclh2o - 29 May 2008 18:16 GMT

Good afternoon,

I have this long formula made of "If's" and got an error for too many
nesting. I have tried to find in library similar problems and could not
exactly find same issue. Could somebody help?

Here it is...

=IF(and(g6=0,i6=0,$m6=0),"",
IF(AND(G6>0,I6=0,$m6=0),"n/r",
IF(and(G6=0,i6=0,$m6>0),0,
IF(and(G6=0,i6>0,$m6>=0),"-0.0",
if(AND((G6>=i6,$m6=0),"",
if(and(g6<i6,$m6=0),-1,
IF(G6-I6<$Q6,(G6-I6)/$Q6,
IF((G6-I6<$Q6+$R6),1+((G6-I6-$Q6)/$R6),
IF(G6-I6<($Q6+$R6+$S6),2+((G6-I6-$Q6-$R6)/$S6),
IF(G6-I6<($Q6+$R6+$S6+$T6),3+((G6-I6-$Q6-$R6-$S6)/$T6),
IF(G6-I6<($Q6+$R6+$S6+$T6+(4*$V6)),4+((G6-I6-$Q6-$R6-$S6-$T6)/(4*$V6)),(G6-I6)/$M6))))))))))))

Signature

Thanks

Reply to this Message

Pete_UK - 29 May 2008 19:31 GMT

You can avoid the nesting errors by constructing your formula
something like this:

=
IF(AND(G6>0,I6=0,$m6=0),"n/r","")&
IF(and(G6=0,i6=0,$m6>0),0,"")&
IF(and(G6=0,i6>0,$m6>=0),"-0.0","")&
if(and(g6<i6,$m6=0),-1,"")&
IF(G6-I6<$Q6,(G6-I6)/$Q6,"")&

and so on. Note that each IF is independent of the others, and the
results get concatenated together.

I've removed a couple of conditions so far which didn't seem to be
doing anything. It's a pity that some of your returns are text ("n/r"
and "-0.0") because this approach will return a text value which can
be converted to a number by multiplying by 1 or adding zero.

Anyway, hopefully this gets you a bit closer.

Hope this helps.

Pete

> Good afternoon,
>
[quoted text clipped - 18 lines]
> --
> Thanks

Reply to this Message

jaclh2o - 30 May 2008 18:55 GMT

Thanks Pete,
Your suggestion eliminated the error message but I am now into "value#"
error. The If's conditions were to prevent possibility of division by 0.
Is there a way to define many formulas that you have as a list and
depending on logical test, you pick one of the formulas? Which function would
help me doing that?

Signature

Thanks

> You can avoid the nesting errors by constructing your formula
> something like this:
[quoted text clipped - 42 lines]
> > --
> > Thanks

Reply to this Message

Pete_UK - 30 May 2008 23:06 GMT

As I pointed out, you have some conditions that return text values and
some which return numeric, but this approach will return a text value
overall. If you then try to do some arithmetic on it in subsequent
calculations you will have to check if the result of this formula is
"n/r" or "-0.0". I'm not sure if you are getting the #VALUE error in
the cell containing your modified IF formula or in some other cell -
what formula have you ended up with instead of your original multi-IF
one?

Pete

> Thanks Pete,
> Your suggestion eliminated the error message but I am now into "value#"
[quoted text clipped - 53 lines]
>
> - Show quoted text -

Reply to this Message