Excel Dynamic Range
At the very bottom a question from LinkedIn with regards an Excel dynamic range to allow the averaging of different data sets without having to manually change the range itself within the formula.
I think the easiest would be to use the OFFSET function. So the OFFSET would go within the average like this
AVERAGE( OFFSET(A Starting Reference cell, Rows to go down, Columns to go across, Height, Width) )
For what you seem to be trying to do you would probably use the Rows and Width option.
Would be easier to explain with some sample data but the video on OFFSET
shows something similar where you tell Excel how many months of sales to add up.
Works very well especially with IRR/ NPV where you want to see what it is over different time periods
Summarised query as per LinkedIn- How to average a range dynamically
“I have this working using a formula but I have to change the range selection when I need to change the month I want to evaluate.
I have a monthly report that I produce that has select financial data – a formatted management report. I am pulling data from a separate workbook that has a full TB for subsidiaries and the consolidated company. The data in the workbook is set up with labels in column “C” there are 24 additional columns that have monthly totals – example heading is “Apr 2014”. This data set has 2328 rows for six subsidiaries and a consolidated company, each company has the same format and has 332 rows. Columns A &B have data that I am using to key off, column “B” is the company and column “A” is a combination of the label and company =B2304&C2304. To help visualize, each company is stacked vertically.
I need to calculate an average of month’s values for a line item such as Sales for the consolidated company. I am currently using this formula =AVERAGE(‘[Consolidated Business Measurements Annual Table.xlsm]2014 Actuals’!$P$2008:$R$2008). Problem is I have to manually change the range each month. I could use 2 drop downs to identify the range but I am not sure how to use this data in a formula to achieve my goal. I am sure this could be done using VBA but not sure where to start.