• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Index & Match to total a 20 week period|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Index & Match to total a 20 week period|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Index & Match to total a 20 wee…
sp_PrintTopic sp_TopicIcon
Index & Match to total a 20 week period
Avatar
Paul Benjamin
Member
Members
Level 0
Forum Posts: 84
Member Since:
July 11, 2017
sp_UserOfflineSmall Offline
1
September 23, 2018 - 8:48 pm
sp_Permalink sp_Print

Hi There,

Each week I need to total the last 20 weeks for each row on the attached, I am using a sum/index formula which works fine but I have to change the formula and drag down every week.

I'm trying to create a sum/index/match formula where all I would need to do is change the week numbers in cells BL1 & BL2 which would eradicate the need to drag formula down.

You can see my attempt in cell BL3, just can not get it to work

Any help gratefully accepted

Thanks

Paul

sp_AnswersTopicSeeAnswer See Answer
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
September 23, 2018 - 11:53 pm
sp_Permalink sp_Print sp_EditHistory

Hi Paul,

Not sure why you use the INDEX function, I don't see any need of it as one and each of the site names are unique.
Anyway, I simplified your formula, as can be seen in cell BO3 in attached file.
In cell BQ3 you will see one solution to get a dynamic approach, you can use other solutions too (non-alcoholic I mean). 😉

The formula you see in cell BQ3 is as below. Do take in mind that in Sweden the semi-colon (;) is the divider whereas comma (,) is the divider for other countries, so the formula below contains the Swedish. When you open the attached file you will see the correct divider as per your regional settings.

=SUM(INDIRECT(ADDRESS(ROW();MATCH($BL$1;$2:$2;0);3;1);1):INDIRECT(ADDRESS(ROW();MATCH($BM$1;$2:$2;0);3;1);1))*100

I don't know if you need any explanation, but I try to write some anyway.

In order to get a dynamic approach, I need to get the cell reference correct for each rows and columns based on what week numbers you are interested in, for that I use the ADDRESS function. The row number is taken from the current row using ROW() function, the column number is taken from the MATCH results from cells BL1 and BM1, where I look in row 2 for a direct match. As the ADDRESS function returns a text value I need to use the INDIRECT function to transform it to valid cell references.

All you have to do now is to write start and end weeks in cells BL1 and BM1 and the calculations in BQ column will dynamically update accordingly.

I hope this helps you get through your obstacle.

Br,
Anders

sp_AnswersTopicAnswer
Answers Post
Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
3
September 24, 2018 - 12:44 am
sp_Permalink sp_Print

Hi Paul

Another formula (there can also be many other methods) is

=SUM(OFFSET(INDIRECT(ADDRESS(ROW(),MATCH($BL$1,$2:$2,0))),0,0,1,20))*100

Just copy it to any column in row 3. You only need to specify the start week in cell BL1, no need for the end week.

The formula will sum 20 weeks. If you need to change the number of weeks, just change the number in red in the formula.

You can also put this number into a cell and refer from it if you want.

I am also using the MATCH function to get the starting week number cell address.

I then use the OFFSET function to sum the number of columns required (20 in this case) beginning from the starting week column.

Cheers

Sunny

Avatar
Frans Visser
Duivendrecht (near Amsterdam) - The Netherlands

VIP
Members
Level 2
Forum Posts: 346
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
4
September 24, 2018 - 7:12 am
sp_Permalink sp_Print

Nice solutions both!

I only don't get it why to sum the percentages? I would have thought an average will give more correct information? But that doesn't harm the formulas now given.

Frans

Avatar
Paul Benjamin
Member
Members
Level 0
Forum Posts: 84
Member Since:
July 11, 2017
sp_UserOfflineSmall Offline
5
September 25, 2018 - 1:32 am
sp_Permalink sp_Print

Many thanks Anders & Sunny very useful & informative solutions.

Frans, we dived another set of 20 week figs by the total of 20 weeks occupancy so we have an average of Revenue per Occ%

Regards

Paul

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
September 26, 2018 - 2:06 am
sp_Permalink sp_Print

Hi Paul,

Glad you found the solutions useful. Even though we use functions that are volatile they are useful and don't make any fuzz in a file with small data loads. But if you by chance get a growing file and start notice longer response times, then it's a good time to see if you can minimize or perhaps even remove the usage of these INDIRECT and OFFSET functions.

Br,

Anders

Avatar
Paul Benjamin
Member
Members
Level 0
Forum Posts: 84
Member Since:
July 11, 2017
sp_UserOfflineSmall Offline
7
September 26, 2018 - 7:25 pm
sp_Permalink sp_Print

Thanks Anders

Avatar
Paul Benjamin
Member
Members
Level 0
Forum Posts: 84
Member Since:
July 11, 2017
sp_UserOfflineSmall Offline
8
October 15, 2018 - 8:19 pm
sp_Permalink sp_Print

Hi Anders,

I have noticed that after every action I do on my spreadsheet, in the Status Bar I get 'Calculating: (4 processor(s)): and there is a long Hang Time before I can move on.

Is this because I have thousands of Indirect & Offset formulas?

Thanks

Paul

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
9
October 16, 2018 - 1:31 pm
sp_Permalink sp_Print

Hi Paul

Having thousands of formulas using INDIRECT and OFFSETS will definitely slow the calculation to a crawl.

Both these functions are volatile and will recalculate after every action as you have discovered.

In your situation, you should consider looking for other alternatives.

You can see if my example.  It uses SUMIFS.

You only need to enter the start week and it will sum 20 weeks for you.

If you still need to use these functions e.g.

=SUM(INDIRECT(ADDRESS(ROW();MATCH($BL$1;$2:$2;0);3;1);1):INDIRECT(ADDRESS(ROW();MATCH($BM$1;$2:$2;0);3;1);1))*100

then maybe you could try putting both the MATCH functions in a cell and refer to them from the formula.

It will only need to calculate once instead of thousand of times if you copy down the original formula.

Hope this helps.

Sunny

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Ahmad Alkhuffash, Chandler Davis, Ramon Lagos, michael serna
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6215
Posts: 27245

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.