Forum Discussion
SUM formula using Absolute Ref to add cells located in rows below, increments when Inserting new row
B1=AVERAGE(INDEX(B:B,5):INDEX(B:B,32))
B2=AVERAGE(INDEX(B:B,5):INDEX(B:B,60))
B3=AVERAGE(INDEX(B:B,5):INDEX(B:B,88))
B4=AVERAGE(INDEX(B:B,5):INDEX(B:B,COUNTA(A:A)))
The above formulas also assume that A1:A4 contain the labels Last 4 weeks, Last 8 weeks, Last 12 weeks, and All time, respectively.
Twifoo wrote:
Assuming you want to obtain the averages from B5:B95, representing the scores from Day 1 to Day 91, the formulas for B1:B4 are these:
B1=AVERAGE(INDEX(B:B,5):INDEX(B:B,32))
B2=AVERAGE(INDEX(B:B,5):INDEX(B:B,60))
B3=AVERAGE(INDEX(B:B,5):INDEX(B:B,88))
B4=AVERAGE(INDEX(B:B,5):INDEX(B:B,COUNTA(A:A)))
The above formulas also assume that A1:A4 contain the labels Last 4 weeks, Last 8 weeks, Last 12 weeks, and All time, respectively.Hi Twifoo,
First off, let me say that I frequent user of Excel but by no means am I a power user/expert. I have grown to be good at basic things and quite often my spreadsheets are printed as reports.
As you can see from the attachment, this is a very simple set of column additions. Each Type of Score has a different Points value, which is calculated in the Total Points column for the 4 Summary Rows. I then divide by the number of weeks to determine the 4 weeks, 8 weeks, 13 weeks and career running averages.
I use the row in between the 2 grey rows to pull in player totals from the This Week's Game Results Page (eg. Feb 20-19). I save these as separate page tabs in the same file so I have a permanent record.
On the individual Player Statistics Pages, my intention then, was to Copy / Insert that row (latest week's results) using absolute values into the Week 1 row (below 2nd grey row). Unfortunately I don't see a way to Insert with absolute values like you can using Paste. So what I am doing instead (more work) - is copy all rows from Week 1 down to the Last Week and Paste on top of Week 2. So Week 1 and Week 2 are identical temporarily. I then do a Copy / Paste (with absolute values) of Latest Week on top of Week 1.
This is quite tedious, but ideally I just wanted this to be 1 step instead of 2 as I have to do this for over 30 players.
- Assuming your screenshot is in A1:U20, the formulas you need are:
F5=SUM(INDEX(F:F,12):INDEX(F:F,15))
F6=SUM(INDEX(F:F,12):INDEX(F:F,19))
F7=SUM(INDEX(F:F,12):INDEX(F:F,24))
F8=SUM(INDEX(F:F,12):INDEX(F:F,63))
F10=INDEX(F:F,12)
S5=MIN(COUNT(E12:E63),52)
S6=MIN(COUNT(E12:E63),13)
S7=MIN(COUNT(E12:E63),8)
S8=MIN(COUNT(E12:E63),4)Hi Twifoo,
The Sum/Index and Min/Count formulas you provided are perfect. They perform exactly what I want to be done and the copy/insert funtion onto Row 12 does not increment the summary calculations.
Thanks so much - it is greatly appreciated.
Just one further question on your suggestion for:
F10=INDEX(F:F,12)
I'm not sure what this is doing on Row 10. I inserted this command and a value of 2 resulted.
Currently, F10 and all the other cells on that row are mapped to cells on another page (This_Week) where I bulk data enter every players results for the current week. So this row (F10 to Q10) will always be displaying the results of the latest week. It also serves as a trigger to let me know which players need an update. When the date hasn't changed, I don't perform the copy/Insert/Paste commands of Row 10 onto Row 12.
I realize this may seem silly, but I don't know MS Access, so I do what I can in Excel.
Ideally, it would be great if I had a Macro that Looks at the value in E10 on the Player Page and if it finds a Date (instead of no date) then perform the Insert on Row 12 for Columns F to Q and then paste in the values values from Row 10. Then have the Macro perform this for all of the individual player pages.
That would be totally awesome because all I would have to do is initiate the Macro. Anyway, this is a world I know nothing about nor whether or not this would even be possible...
Regardless, what you have provided is fantastic.
Regards,
Rob
