Automating Entire Column and Row Averages with Specific Format

[JuliusV]

Hi All,

I am trying to make a monthly capacity summary that is pulling data from a very specific format within a weekly capacity tracker. I am running into challenges on how I can automate and make it dynamic across a large number of employees and over many months.

Within the spreadsheet, I have two tabs:
(1) Capacity Summary - This is the sample summary I am trying to create and I manually put in formulas that would calculate the averages I was looking for
(2) Capacity Tracker - This is a sample of where I am pulling data from with a focus on values after row 12. There are two types of roles I am trying to calculate for across Consultant and Engineer. For each role there is the same set of data across 9 rows that I am trying to use to pull in the average that would display in the Capacity Summary.

I originally attempted to do offsets across an entire column and row range with sumifs, but from what I understand it is not possible to do an offset when selecting a whole range.

Appreciate any help!

 

[Hui]

On the capacity Summary Sheet
Cell B2: Type 1/9/2021 ie: A date
You can apply a Custom Number format of DDDD

Cell B4:
=SUMPRODUCT(('Capacity Tracker Total'!$D$12:$R$100) * ('Capacity Tracker Total'!$B$12:$B$100="Total Number of Users Servicing") * (MONTH('Capacity Tracker Total'!$D$3:$R$3)=MONTH($B$2))) / SUMPRODUCT(('Capacity Tracker Total'!$B$12:$B$100="Total Number of Users Servicing") * (MONTH('Capacity Tracker Total'!$D$3:$R$3)=MONTH($B$2)))

You can work out the rest from there

 

[JuliusV]

Thank you for the assistance. I was wondering would there be any issues if I were to select the whole matrix from ='Capacity Tracker Total'!D:R?

 

[p45cal]

In the attached, see pivot table at cell A22 of the Capacity Summary sheet.
There's another one (only a different layout) at cell A60
These are based (indirectly) on the table on a copy of your Capacity Tracker Total sheet called Capacity Tracker Total (3).

 

[Hui]

Thank you for the assistance. I was wondering would there be any issues if I were to select the whole matrix from ='Capacity Tracker Total'!D:R?

You can, but it results in lots of superfluous calculations
Better to simply double the rows/columns you need

 

[JuliusV]

Thanks for the clarification. One more question that I had is how can I differentiate between the two roles for Consultant and Engineer. I found a work around where in the Capacity Tracker I had a new column that has all Consultant or Engineer across the rows where it ties to the line items I care about (Column B). However, this seems to be a clunky solution and I was wondering if there is some offset formula that would allow me to calculate the averages without adding this helper column B.

 

[p45cal]

it's as if I didn't exist.

 

[pecoflyer]

You can, but it results in lots of superfluous calculations
Better to simply double the rows/columns you need

Or you could make your range an Excel Table that expands and retracts automatically within the formula

 

[pecoflyer]

it's as if I didn't exist.

Over the years I noticed that OP's usually stick to the latest answer that agrees with them

 

[p45cal]

Over the years I noticed that OP's usually stick to the latest answer that agrees with them

I realise; it's just a grumble that often, OPs don't bother to acknowledge when people try to help them.
I suspect my offering didn't agree with them because it uses techniques that may be unfamiliar. My offering not only differentiates between the 2 roles of Engineer and Consultant, but if additional roles in the future are introduced there'd be no change whatsoever needed to add those new roles (apart from refreshing the pivot).

 

[Hui]

I realise; it's just a grumble that often, OPs don't bother to acknowledge when people try to help them.
I suspect my offering didn't agree with them because it uses techniques that may be unfamiliar. My offering not only differentiates between the 2 roles of Engineer and Consultant, but if additional roles in the future are introduced there'd be no change whatsoever needed to add those new roles (apart from refreshing the pivot).

P45Cal

You forgot to add [/Rant] at the end

Your summation is unfortunately very true.

I got over this a long time ago, when I realised that I get more out of this solving problems in different ways, to help both me as well as the other readers than the OP is probably getting from it

Chandoo.org has a large following who follow all answers, and if the answer in some way helps them or myself as the respondent, that makes me happy.

It is the shear variety of data sets that w get exposed to here that makes solving problems and then seeing how others have solved them in different ways, makes the forums so valuable. [/End Rant 2]

Thank you for your ongoing support

 

[JuliusV]

I do appreciate the other responses. There is some pivot tables manipulation that you shared that I haven't done in years.

The reason I am working on continue to clarify on the formula response from from Hui is because there are no excel table equivalents when I port over to other applications such as google sheets. I am limited to the formula methodology which I have been able to apply in a less sophisticated way so far.