Log In
Register
Home
July 11, 2017
Offline
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
See Answer
VIP
Trusted Members
December 7, 2016
Offline
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
Answers Post
VIP
Trusted Members
June 25, 2016
Offline
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
VIP
April 21, 2015
Offline
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
July 11, 2017
Offline
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
VIP
Trusted Members
December 7, 2016
Offline
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
July 11, 2017
Offline
Thanks Anders
July 11, 2017
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
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
1 Guest(s)
