Formula you typed contains an ERROR message - HELP

[NS]

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

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

Nasty - but it works! YEAH!

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

N.

 

[Bob Phillips]

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)