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

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

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

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

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

Thanks Anders

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

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