Nesting Eroor - more than 64
 

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(G60,I6=0,$m6=0),"n/r",
IF(and(G6=0,i6=0,$m60),0,
IF(and(G6=0,i60,$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))))))))))))

--
Thanks

Nesting Eroor - more than 64
 

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

=
IF(AND(G60,I6=0,$m6=0),"n/r","")&
IF(and(G6=0,i6=0,$m60),0,"")&
IF(and(G6=0,i60,$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

On May 29, 6:16*pm, jaclh2o wrote:
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(G60,I6=0,$m6=0),"n/r",
IF(and(G6=0,i6=0,$m60),0,
IF(and(G6=0,i60,$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))))))))))))

--
Thanks

Nesting Eroor - more than 64
 

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?
--
Thanks


"Pete_UK" wrote:

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

=
IF(AND(G60,I6=0,$m6=0),"n/r","")&
IF(and(G6=0,i6=0,$m60),0,"")&
IF(and(G6=0,i60,$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

On May 29, 6:16 pm, jaclh2o wrote:
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(G60,I6=0,$m6=0),"n/r",
IF(and(G6=0,i6=0,$m60),0,
IF(and(G6=0,i60,$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))))))))))))

--
Thanks

Nesting Eroor - more than 64
 

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

On May 30, 6:55*pm, jaclh2o wrote:
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?
--
Thanks



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

=
IF(AND(G60,I6=0,$m6=0),"n/r","")&
IF(and(G6=0,i6=0,$m60),0,"")&
IF(and(G6=0,i60,$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

On May 29, 6:16 pm, jaclh2o wrote:
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(G60,I6=0,$m6=0),"n/r",
IF(and(G6=0,i6=0,$m60),0,
IF(and(G6=0,i60,$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))))))))))))

--
Thanks- Hide quoted text -

- Show quoted text -