Sum Different Rows & Columns

  • Thread starter Thread starter tally
  • Start date Start date
T

tally

I need some assistance with making the following calculation. Eac
week a person shoots a score. They can have anywhere from 1-12 score
over a 12 week period, but no more than 1 per week.

Each week a calculation is made to generate "bonus points" which ar
added the next time they register a score.

I need to find a way to:

1) Locate the current week's score for each unique person.

2) Add the "bonus points" calculated in their previous record (which i
tabulated in a different column).

The data is in the following format:

Date Person Score Total Bonus
15-Jan Mike 10 10 5
15-Jan Jane 15 15 3
22-Jan Mike 17 22 2
22-Jan Jane 10 13 4
27-Jan Jane 15 19 3
27-Jan Mike 5 7 8

Thanks in advance for your assistance
 
Dear Tally

My suggestion would be to create a pivot table based on
the sheet containing the scores (The sheet containing the
sample data you provided).

Firstly I would insert two new fields into your
data, "Week Number" and "Period Number"
In the "Week Number" field enter =WEEKNUM(C2,2) where C2
is the first cell containg the date.
In the "Period Number" field enter =IF(B2 <12,"Set 1",IF
(B2 <12>=24,"Set 2",IF(B2 <24>=36,"Set 3","Set 4"))) where
B2 is the first cell containing the week number calculated
above. You can obviously change the week number parameters
in the formula above to suit the range that you want. Copy
and paste bot of these formulas into as many cells down as
you have dates for.

The reason for adding these two new fields is that they
will be handy for using in the pivot table.

Inset a pivot table on a new worksheet. Use $A$:$G$ as the
range.this will show all the data that you want,
summarised by person and week number. In my test I used
the week number as a column field, the Person as the row
field, and both the score and bonus in the data area. I
put the Period number in the page field area, but it could
just as easily be placed as a row field.

I hope this goes some way to solving your problem

Paul

If you need more detail (eg a step by step based on the
above) please feel free to email me direct.

Kind regards

Paul
 
I have to run the calculation each week for each of the 120 people an
then run a weekly subtotal so I'm not sure a pivot table is the best.
 
Back
Top