SUM formula using Absolute Ref to add cells located in rows below, increments when Inserting new row

Question

My spreadsheet will be maintaining statistics for player performance in a sports league. I will be maintaining multiple Summary Rows (1 to 4) that calculate Last 4 Weeks, Last 8 Weeks, Last 12 Weeks, and All Time statistics. I want the rows to always be the most recent week first and then going back in time sequentially for all prior weeks and I need those 4 Summary Rows to be located at the top of the spreadsheet.

So when I Insert a new row onto Row 5 - I don't want the Absolute References of the formulas in the Summary Rows (located above) to change. It needs to always remain starting with Row 5.

Unfortunately it does change. All other help sources I have gone to only deal with the scenario of the Summary Row being located below the rows being added.

I have the summary rows located at the top because I want to be able to print off these as reports for a player showing his summary rows at the top followed by the most recent weekly performance and prior weeks listed below.

robfaas22 · Answer

Hi Twifoo,&nbsp;The Sum/Index and Min/Count formulas you provided are perfect.&nbsp; They perform exactly what I want to be done and the copy/insert funtion onto Row 12 does not increment the summary calculations.&nbsp;Thanks so much - it is greatly appreciated.&nbsp;Just one further question on your suggestion for:F10=INDEX(F:F,12)I'm not sure what this is doing on Row 10.&nbsp; 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.&nbsp; So this row (F10 to Q10) will always be displaying the results of the latest week.&nbsp; It also serves as a trigger to let me know which players need an update.&nbsp; When the date hasn't changed, I don't perform the copy/Insert/Paste commands of Row 10 onto Row 12.&nbsp;&nbsp;&nbsp;I realize this may seem silly, but I don't know MS Access, so I do what I can in Excel.&nbsp;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.&nbsp; Then have the Macro perform this for all of the individual player pages.&nbsp;That would be totally awesome because all I would have to do is initiate the Macro.&nbsp; Anyway, this is a world I know nothing about nor whether or not this would even be possible...&nbsp;&nbsp;Regardless, what you have provided is fantastic.&nbsp;Regards,&nbsp;Rob&nbsp;

twifoo · Answer

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.

robfaas22 · Answer

Twifoo&nbsp;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.&nbsp; I have grown to be good at basic things and quite often my spreadsheets are printed as reports.&nbsp;As you can see from the attachment, this is a very simple set of column additions.&nbsp; Each Type of Score has a different Points value, which is calculated in the Total Points column for the 4 Summary Rows.&nbsp; I then divide by the number of weeks to determine the 4 weeks, 8 weeks, 13 weeks and career running averages.&nbsp;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).&nbsp; I save these as separate page tabs in the same file so I have a permanent record.&nbsp;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).&nbsp; Unfortunately I don't see a way to Insert with absolute values like you can using Paste.&nbsp; 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.&nbsp; So Week 1 and Week 2 are identical temporarily.&nbsp; I then do a Copy / Paste (with absolute values) of Latest Week on top of Week 1.&nbsp;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.&nbsp; &nbsp;&nbsp;

twifoo · Answer

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)

twifoo · Answer

Hello Rob,I thought Row 10 comes from Row 12 so I suggested the formula. Such formula always returns data from Row 12, regardless of whether or not you inserted a new Row on Row 12.I’m glad I have somehow helped you.Cheers,Twifoo

Forum Discussion

SUM formula using Absolute Ref to add cells located in rows below, increments when Inserting new row

Resources