Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Aggregation Aggravation (XP)

    Last year, Steve kindly created a spreadsheet which calculated an aggregate score from a number of pieces of work based on certain criteria. New courses are now offered with different aggregation criteria. These are:

    Pupils produce 3 pieces of work, one each from Biology, Chemistry and Physics. They are each given a mark for P (Planning); O (Observing); A (Analysing) and E (Evaluating).
    A mark for each skill area must be included in the aggregation.
    A maximum of 2 pieces of work are selected - one piece of work could supply all the marks.

    I would need some indication of which pieces of work supplied the aggregated marks.

    I have attached a small spreadsheet outlining the general format.

    The original post can be found here. <!post=description,219271>description<!/post>

    Many thanks
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    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: Aggregation Aggravation (XP)

    Boy this is much simpler than the original posting. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    If I understand completely, the attached should solve the problem. I based it on the original, just adding a new sol'n matrix and then "fixing" the calcs. and eliminating the redundant info
    Only 3 tests make the total possibilites only 81 and the solution matrix is just over half of these. You only have to eliminate those solutions that use all three values. You might want to check the soln matrix. i found 45 and you listed only 39
    It seems to me you missed:
    <table border=1><td align=center valign=bottom>Test</td><td align=center valign=bottom>1</td><td align=center valign=bottom>1</td><td align=center valign=bottom>1</td><td align=center valign=bottom>1</td><td align=center valign=bottom>2</td><td align=center valign=bottom>2</td><td align=center valign=bottom>2</td><td align=center valign=bottom>2</td><td align=center valign=bottom>3</td><td align=center valign=bottom>3</td><td align=center valign=bottom>3</td><td align=center valign=bottom>3</td><td align=center valign=bottom>Subject</td><td align=center valign=bottom>B</td><td align=center valign=bottom>B</td><td align=center valign=bottom>B</td><td align=center valign=bottom>B</td><td align=center valign=bottom>P</td><td align=center valign=bottom>P</td><td align=center valign=bottom>P</td><td align=center valign=bottom>P</td><td align=center valign=bottom>P</td><td align=center valign=bottom>P</td><td align=center valign=bottom>P</td><td align=center valign=bottom>P</td><td align=center valign=bottom>Skill</td><td align=center valign=bottom>P</td><td align=center valign=bottom>O</td><td align=center valign=bottom>A</td><td align=center valign=bottom>E</td><td align=center valign=bottom>P</td><td align=center valign=bottom>O</td><td align=center valign=bottom>A</td><td align=center valign=bottom>E</td><td align=center valign=bottom>P</td><td align=center valign=bottom>O</td><td align=center valign=bottom>A</td><td align=center valign=bottom>E</td><td align=center valign=bottom></td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=center valign=bottom></td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=center valign=bottom></td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=center valign=bottom></td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=center valign=bottom></td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=center valign=bottom></td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>1</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td><td align=right valign=bottom>0</td></table>
    The calculations become much simpler since you don't have to split the solution matrix because of their large size.

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aggregation Aggravation (XP)

    Superb! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> . You are absolutely correct about my missing the six matrix rows. I thought there was something wrong when I checked my initial matrix but I could not see them. Steve, my grate as never been so full.
    One last request <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. If you have the time and inclination, would you run through the array formulae you used? I want to get my head around them so I can try to create my own solution should (when) the criteria change again - 4 times in 4 years!!

    Again, many thanks.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    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: Aggregation Aggravation (XP)

    S4:
    TRANSPOSE($C4:$N4))
    Converts the 1 row 12 column array into a 12 row x 1 Column Array

    MMULT(AnswerMatrix,TRANSPOSE($C4:$N4))
    Multiplies the answermatrix (45rows x 12 columns) by "above" 12x1 array giving a 45 row x 1 col result "possible solutions"

    =MAX(MMULT(AnswerMatrix,TRANSPOSE($C4:$N4)))
    Gives the max of the 45 element array (which is the answer)

    T4:
    ROW(INDIRECT("1:8")
    gives the numbers 1-8 in an array

    LARGE(C4:N4,ROW(INDIRECT("1:8"))))
    Gets an 8 element array of the largest 8 values in the range C4:N4

    =SUM(LARGE(C4:N4,ROW(INDIRECT("1:8"))))
    Gets the sum of the 8 largest elements

    U4
    MMULT(AnswerMatrix,TRANSPOSE($C4:$N4)),0)
    See S4 for explanation. a 45 element array of all the possibilities

    =MATCH($S4,MMULT(AnswerMatrix,TRANSPOSE($C4:$N4)), 0)
    Finds the first time that the max(S4) appears in the array of values.

    O4:R4
    INDEX(AnswerMatrix,U4,0)
    Gets the "1 row" (U4) of the answer matrix (the "zero column" chooses all of the columns), This is 12 values 1 or 0

    INDEX(AnswerMatrix,U4,0)*(Scores!$C$1:$N$1)
    Gets the positions of the "chosen skill" it will be the "value" in C1:N1 (1-4) if the number is chosen or 0 if not. This is a 12 elelment row

    INDEX(AnswerMatrix,U4,0)*$C4:$N4)
    Gets the positions of the "chosen elements" it will be the "value" in C4:N4 if the number is chosen or 0 if not

    IF((INDEX(AnswerMatrix,U4,0)*(Scores!$C$1:$N$1))=S cores!O$1,INDEX(AnswerMatrix,U4,0)*$C4:$N4)
    This compares each of the 12 elelments against the column heading (1-4) and for each one that is equal grabs the "chosen values"

    =MAX(IF((INDEX(AnswerMatrix,U4,0)*(Scores!$C$1:$N$ 1))=Scores!O$1,INDEX(AnswerMatrix,U4,0)*$C4:$N4))
    This grabs the max of the chosen values for the chosen skill (column header O1). It is similar to PQR columns.

    I assume that the INDEX formulas used in Verification are more straightforward and I won't go into those.
    It actually helps to create the intermediate arrays to see how they are used.

    Hope this helps,

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aggregation Aggravation (XP)

    It certainly does help, Steve. I shall scrutinise these carefully.
    Again, many thanks.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  6. #6
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aggregation Aggravation (XP)

    Steve, would you please take a look at the attached file? I have tried to produce a summary of the aggregate scores and their sources but with partial success (Solutions worksheet). It works for the first 45 students but not thereafter since the AnswerMatrix only contains 45 rows. In fact I have 93 students to enter for this year. It may be more next year. Can you find a way round this?

    Many thanks.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  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: Aggregation Aggravation (XP)

    Not sure what you are after exactly. Why not use cond formatting on the scores sheet directly to "highlight the chosen ones"

    See attached.

    Steve

  8. #8
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aggregation Aggravation (XP)

    That's exactly what I wanted. The conditional formatting would have been easy but I had problems generating the binary solution matrix on the RHS of the worksheet.

    Thanks a bundle, Steve!
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  9. #9
    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: Aggregation Aggravation (XP)

    Your "problem" was that you were trying to pull an "solution" from the answer matrix based on the row number of the person (which is why you had problems after 45 people). The person # and the answer have nothing to do with one another. You have to grab the "row" value from the last column in the table. This row is the row from the answer matrix that is a first max (there may be more than 1 max)

    I indexed with this row, then did a cond format with the results.

    Steve

  10. #10
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aggregation Aggravation (XP)

    I hadn't got my head around your formulae so I was too literal in my attempts. However, thanks to your assistance, I have grasped the key features of your solution and have been able to do similar but with a different answer matrix. I am now confident to use this method when the aggregation criteria change - which they will. <img src=/S/groan.gif border=0 alt=groan width=16 height=15>. In other words, incredibly, I'd worked out where I'd gone wrong.
    Thanks Steve, for your help and patience.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

Posting Permissions

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