error
Posted by Jackie on January 25, 2002 7:49 AM
as I was writting a formula,an error came up that the formula is too long and didn't alaw me to continue the formula how can I solve this problem and write the rest of my formula
Posted by Aladin Akyurek on January 25, 2002 7:56 AM
Care to post the formula you tried to set up?
Posted by jackie on January 27, 2002 3:02 AM
=SUMPRODUCT(($B$2:$B$117="Female")*($C$2:$C$117="18-24")*(($E$2:$E$117="")+($E$2:$E$117="Finger 1att")+($E$2:$E$117="Finger 2 att"))*(($F$2:$F$117="")+($F$2:$F$117="Voice 1att")+($F$2:$F$117="Voice 2 att"))*(($G$2:$G$117="")+($G$2:$G$117="Face 1 att")+($G$2:$G$117="Face 2 att"))*(((($H$2:$H$117="Finger 2 att")*($I$2:$I$117="Voice 2 att"))+(($H$2:$H$117="Finger 2 att")*($I$2:$I$117="Voice 3 att"))+(($H$2:$H$117="Finger 1 att")*($I$2:$I$117="Voice 3 att"))+(($H$2:$H$117="Finger 2 att")*($I$2:$I$117="Voice 1 att"))+(($H$2:$H$117="Finger 3 att")*($I$2:$I$117="Voice 1 att")))+((($L$2:$L$117="Finger 1 att")*($M$2:$M$117="Voice 1 att"))+(($L$2:$L$117="Finger 1 att")*($M$2:$M$117="Voice 2 att"))+(($L$2:$L$117="Finger 2 att")*($M$2:$M$117="Voice 1 att"))+(($L$2:$L$117="Finger 1 att")*($M$2:$M$117="Voice 3 att"))+(($L$2:$L$117="Finger 3 att")*($M$2:$M$117="Voice 1 att")))))
Posted by jackie on January 27, 2002 3:52 AM
=SUMPRODUCT(($B$2:$B$117="Female")*($C$2:$C$117="18-24")*(($E$2:$E$117="")+($E$2:$E$117="Finger 1att")+($E$2:$E$117="Finger 2 att"))*(($F$2:$F$117="")+($F$2:$F$117="Voice 1att")+($F$2:$F$117="Voice 2 att"))*(($G$2:$G$117="")+($G$2:$G$117="Face 1 att")+($G$2:$G$117="Face 2 att"))*(((($H$2:$H$117="Finger 2 att")*($I$2:$I$117="Voice 2 att"))+(($H$2:$H$117="Finger 2 att")*($I$2:$I$117="Voice 3 att"))+(($H$2:$H$117="Finger 1 att")*($I$2:$I$117="Voice 3 att"))+(($H$2:$H$117="Finger 2 att")*($I$2:$I$117="Voice 1 att"))+(($H$2:$H$117="Finger 3 att")*($I$2:$I$117="Voice 1 att")))+((($L$2:$L$117="Finger 1 att")*($M$2:$M$117="Voice 1 att"))+(($L$2:$L$117="Finger 1 att")*($M$2:$M$117="Voice 2 att"))+(($L$2:$L$117="Finger 2 att")*($M$2:$M$117="Voice 1 att"))+(($L$2:$L$117="Finger 1 att")*($M$2:$M$117="Voice 3 att"))+(($L$2:$L$117="Finger 3 att")*($M$2:$M$117="Voice 1 att")))))
Posted by Aladin Akyurek on January 27, 2002 5:21 AM
Jackie --
The formula is indeed too long (exceeds a limit Excel observes). But, I think, that can be circumvented. Looking at your formula, I was thinking what this formula is supposed to compute?
Apperently, you have
in B values like "Female", "Male",
in C values like "18-24",
in E, H, and L values like "Finger 1 Att", "Finger 2 att",
in F, I, and M values like "Voice 1 Att", "Voice 2 att",
In G values like "Face 1 Att", "Face 2 att".
You've also blank cells in (some of) these columns. Are the blanks created by formulas like =IF(...,"") or some cells are just empty (contains no entry/no formula)?
And, more importantly, What does the formula is intended to compute?
Aladin
==========
Posted by Jackie on January 28, 2002 2:52 AM
The formula is suppose to count the number of females with a certain age that satisfies certain conditions in the other cells.
the empty cells means that it is neither Finger 1 att or Finger 2 att or Finger 3 att.
Jackie
Posted by Aladin Akyurek on January 28, 2002 8:47 AM
I'll try to give an example of multiconditonal count with SUMPRODUCT, which appears to fit your situation.
Lets say that we have Gender in A, computed Age (17,20,18,21,24,etc) in B, Employed (with as value either Yes or No) in C, Salary in D, and Status (either Married or Single) in E.
Suppose I want to count females with
Age >= 20 and <=30,
Employed = Yes,
Salary > 30000,
Status = Single.
The formula:
=SUMPRODUCT((A2:A10="Female")*(B2:B10 >= 20)*(B2:B10 <= 30)*(C2:C10="Yes")*(D2:D10 > 30000)*(E2:E10="Single"))
will produce the desired count. (* in this formula is a Boolean AND; + is used when an OR needed.). The formula as written will not be effected (will produce no errors even if some cells are empty). But, if there are cells that contain formulas that return blanks "", there will be a #VALUE! error. The formula can be modified to cope with such blanks too.
Does this help?
Aladin
