Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- 464K Members
- 11.3K Online
- 560K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- SUM formula using Absolute Ref to add cells located in rows below, increments when Inserting new row

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- SUM formula using Absolute Ref to add cells located in rows below, increments when Inserting new row

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-23-2019 01:28 PM

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.

Labels:

5 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-23-2019 07:10 PM

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.

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-24-2019 03:54 AM

@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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-25-2019 10:53 PM

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)

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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-26-2019 07:47 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-26-2019 06:22 PM

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

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

Related Conversations

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
35.3K
Views

7 Likes

35 Replies

flashing a white screen while open new tab

Deleted
in
Discussions
on
10-05-2019
31.1K
Views

14 Likes

14 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
181K
Views

8 Likes

29 Replies

Security Community Webinars

Valon_Kolica
in
Security, Privacy & Compliance
on
10-22-2019
16.5K
Views

12 Likes

13 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft