Formula you typed contains an ERROR message - HELP

  • Thread starter Thread starter NS
  • Start date Start date
N

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
 
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)
 
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)
 
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)
 
Back
Top