Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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 26-May-03 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 ctrl-shift-enter) 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

  4. #4
    5 Star Lounger
    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.

  5. #5
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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 "Matrix-math" (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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

  9. #9
    Star Lounger
    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 simple-minded. 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •