Results 1 to 9 of 9

20030526, 21:49 #1
 Join Date
 Oct 2001
 Location
 San Bernardino, California, USA
 Posts
 734
 Thanks
 0
 Thanked 0 Times in 0 Posts
yet another multiple condition challenge (97  2002)
Yet again I am faced with multiple conditions when trying to count. The situation is that in a school, students are scheduled for tests of various days. Some students take one test, others take multiple. A student only needs ONE answer sheet per day regardless the number of tests taken that day. I am trying to develop a formula that will compare testing periods to determine if a student is testing so I can count the number of answer sheets required. In the attached example (a very small example), it should be clear that on 5/27/03 there are nine answer sheets needed by the teacher (Bigham) since there are nine individual students testing that day although they are taking more than nine tests. On 5/28/03, there are only six answer sheets required.
I need to count the number of answer sheets required, per day, by teacher. The actual spreadsheet has as many approx 3000 students and 150 teachers.
I thought some variation of an array formula that counts students with at least one non blank cell during a test date per teacher. However, my brain hurts after thinking about it all day. Help!

20030526, 23:11 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: yet another multiple condition challenge (97  2002)
Jan Karel or Steve will probably come up with something better, but here is my attempt. It needs an intermediate column for each day (which may be hidden, however). It contains formulas like
=(COUNTA(B33)>0)
in cell I3 to determine if there is at least one test on a day for a student. I use an array formula to get the count for a given teacher:
=SUM(($H$3:$H$22=$L3)*I$3:I$22)
(the name of the teacher is in cell L3.) See attached workbook.

20030527, 01:21 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: yet another multiple condition challenge (97  2002)
<P ID="edit" class=small>(Edited by sdckapr on 26May03 22:21. Edited to put in improved, simpler formula)</P>Put the dates in K1, L1, M1 etc
Put the teacher names in J2, J3, J4, J5, etc
Try this ARRAY formula(confirm with ctrlshiftenter) in K2 (one line)
=SUM(IF(MMULT((IF((NOT((ISBLANK($B$3:$G$12))))*($H $3:$H$12=$J2),1,0)), TRANSPOSE(IF(($B$1:$G$1=K$1),1,0)))>0,1,0))
Copy this into as many columns and rows as appropriate.
Change the range lengths as appropriate (A3:a12) is student list,
B1:G1 = dates, B3:g12 is whole range of data, H3:H12 is teacher list
Use range names if appropriate.
I will leave it as a "learning exercise" to figure out how and why it works!
Steve

20030527, 05:47 #4
 Join Date
 Oct 2001
 Location
 San Bernardino, California, USA
 Posts
 734
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: yet another multiple condition challenge (97  2002)
Thanks  it does work, of course. I don't understand why or how. I could not help but notice that your post was edited to put in an improved, simpler formula. All I can say is, this is absolutely not simple. Maybe improved but not simple. Thanks again.

20030527, 06:17 #5
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: yet another multiple condition challenge (97  2002)
Neat. Good thinking.
And no, I'm not going to try and simplify <g>.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030527, 06:43 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: yet another multiple condition challenge (97  2002)
Steve didn't state that the formula was simple, only that is was simpler than the one it replaces <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>.

20030527, 08:55 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: yet another multiple condition challenge (97  2002)
Right you are, Hans (simpler, not simple). I don't think ANY of the arrays formulas are "simple" and this one comes with the added "bonus" of having some "Matrixmath" (I could say it was in honor of the new movie, but it seemed to be the "simplest" way to accomplish what Don requested.)
If anyone caught my original it had a few extra portions, that I realized after posting, they could be combined differently and some of them removed. Some just come about when I build/test them and then forget to remove after it works.
Steve

20030527, 13:19 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: yet another multiple condition challenge (97  2002)
Here is yet another "simpler" formula. I got rid of 3 "unneeded" IFs.
=SUM((MMULT(((NOT((ISBLANK($B$3:$G$12))))*($H$3:$H $12=$J2)), TRANSPOSE($B$1:$G$1=K$1)*1)>0)*1)
I don't think I can get it too much simpler, with the given constraints of the problem, but I am open to suggestions.
Steve

20030527, 17:26 #9
 Join Date
 Jun 2002
 Posts
 98
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: yet another multiple condition challenge (97  2002)
Hey Don,
I am in awe of the elegant and mathematical formulas that complex minds concoct. But I'm a simple country girl. Some would say simpleminded. So this is how I'd solve your problem: a couple columns like the ones suggested before to get daily counts and then a Pivot Table to do all the work of adding it up by teacher for me. Simple AND lazy!
See attachment__________________________________________________ ____
<img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA