Formula you typed contains an ERROR message - HELP

Hi Everyone -

I am trying to enter the following formula:
*****************************************************************
=IF($C7="SIL",0,
IF((AND(OR($D7="Whole Shelled",$D7="Ground
Shelled"),$R7<15.5,$R7>=0)),1,
IF((AND($D7="Ground Cob",($R7-5)<15.5,$R7>=0)),0.5165,
IF((AND($D7="Whole Shelled",$R7>=50)),0.5572,
IF((AND($D7="Ground Shelled",$R7>=50)),0.6288,
IF((AND($D7="Ground Cob",($R7-5)>50)),0.3016,
IF(AND($D7="Ground Cob",$R7-5<=50,$R7-5>=15.5),
INDEX(Tables!$A$131:$D$166,MATCH(($R7-5),Tables!$A$131:$A$166),MATCH($D7,Tables!$A$130:$D$130)),
INDEX(Tables!$A$131:$D$166,MATCH($R7,Tables!$A$131:$A$166),MATCH($D7,Tables!$A$130:$D$130)))))))))
******************************************************************
and am getting "The formula you typed contains an error." message. I
believe it is because I am exceeding the maximum number of arguments
allowed in a function (30). Can anyone tell me if this is why I am
getting the message? And also - does anyone have any suggestions as to
how I can get around this error????

PLEASE HELP!

Thanking anyone in advance for passing along their two cents!

Nadine

You are correct.

One simple way. Resolve part in one cell, with a final option of "" then in
the next (this) cell test for "", if true do the other tests, else pick it
up.

Say as an example

M1:
=IF(Cond1,v1,IF(Cond2,v2,IF(cond3,v3,IF(Cond4,v4,IF(Conde5,v5,IF(Cond6,v6,IF
(Cond7,V7,""))))))))

N1: =IF(M1<>"",M1,IF(Cond8,v8,IF(Cond9,v9, etc.)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"NS" <[email protected]> wrote in message
news:[email protected]...
> Hi Everyone -
>
> I am trying to enter the following formula:
> *****************************************************************
> =IF($C7="SIL",0,
> IF((AND(OR($D7="Whole Shelled",$D7="Ground
> Shelled"),$R7<15.5,$R7>=0)),1,
> IF((AND($D7="Ground Cob",($R7-5)<15.5,$R7>=0)),0.5165,
> IF((AND($D7="Whole Shelled",$R7>=50)),0.5572,
> IF((AND($D7="Ground Shelled",$R7>=50)),0.6288,
> IF((AND($D7="Ground Cob",($R7-5)>50)),0.3016,
> IF(AND($D7="Ground Cob",$R7-5<=50,$R7-5>=15.5),
>
INDEX(Tables!$A$131:$D$166,MATCH(($R7-5),Tables!$A$131:$A$166),MATCH($D7,Tab
les!$A$130:$D$130)),
>
INDEX(Tables!$A$131:$D$166,MATCH($R7,Tables!$A$131:$A$166),MATCH($D7,Tables!
$A$130:$D$130)))))))))
> ******************************************************************
> and am getting "The formula you typed contains an error." message. I
> believe it is because I am exceeding the maximum number of arguments
> allowed in a function (30). Can anyone tell me if this is why I am
> getting the message? And also - does anyone have any suggestions as to
> how I can get around this error????
>
> PLEASE HELP!
>
> Thanking anyone in advance for passing along their two cents!
>
> Nadine
>

Thanks for your reply.

The maximum number of nested IF's is seven, as far as my understanding
goes....correct? So - it is the other functions within the IF's that
are pushing my formula past the maximum number of arguments in a
function? I guess I do not have a clear understanding of what an
"argument" is (well in EXCEL that is....).

Does anyone know of a way for me to resolve this issue all in the same
cell without breaking it out into two formulas?

Thanks again for helping me out!

Nadine

You could try this particularly nasty alternative

=IF($C7="SIL",0,"")&
IF((AND(OR($D7="Whole Shelled",$D7="Ground
Shelled"),$R7<15.5,$R7>=0)),1,"")&
IF((AND($D7="Ground Cob",($R7-5)<15.5,$R7>=0)),0.5165,"")&
IF((AND($D7="Whole Shelled",$R7>=50)),0.5572,"")&
IF((AND($D7="Ground Shelled",$R7>=50)),0.6288,"")&
IF((AND($D7="Ground Cob",($R7-5)>50)),0.3016,"")&
IF(AND($D7="Ground Cob",$R7-5<=50,$R7-5>=15.5),
IF(ISNA(INDEX(Tables!$A$131:$D$166,MATCH(($R7-5),Tables!$A$131:$A$166),MATCH
($D7,Tables!$A$130:$D$130))),"",
INDEX(Tables!$A$131:$D$166,MATCH(($R7-5),Tables!$A$131:$A$166),MATCH($D7,Tab
les!$A$130:$D$130))),
IF(ISNA(INDEX(Tables!$A$131:$D$166,MATCH($R7,Tables!$A$131:$A$166),MATCH($D7
,Tables!$A$130:$D$130))),"",
INDEX(Tables!$A$131:$D$166,MATCH($R7,Tables!$A$131:$A$166),MATCH($D7,Tables!
$A$130:$D$130))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"NS" <[email protected]> wrote in message
news:[email protected]...
> Thanks for your reply.
>
> The maximum number of nested IF's is seven, as far as my understanding
> goes....correct? So - it is the other functions within the IF's that
> are pushing my formula past the maximum number of arguments in a
> function? I guess I do not have a clear understanding of what an
> "argument" is (well in EXCEL that is....).
>
> Does anyone know of a way for me to resolve this issue all in the same
> cell without breaking it out into two formulas?
>
> Thanks again for helping me out!
>
> Nadine
>

Nasty - but it works! YEAH!

Thanks for your help, Bob! You're awesome!

N.

BTW, although you only had 7 IFs, the INDEX & MATCH functions were the
reason it was pushed to more than 7 nested functions (which is the real
error, not > 7 IFs)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"NS" <[email protected]> wrote in message
news:[email protected]...
> Nasty - but it works! YEAH!
>
> Thanks for your help, Bob! You're awesome!
>
> N.
>