Two dimensional dynamic named range over a pivot table not working as I thought

ashleighbrown

New Member
Joined
Mar 10, 2023
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
Hello,

Bear with me because I feel like I'm going to give quite an extensive explanation to contextualise why I'm trying to do this as I think it will help with understanding..

So, we have a Microsoft Form that staff complete every time an incident occurs, so there are numerous rows for each individual incident in the original raw data. From that, we have numerous pivot tables, one of these is to look at how many incidents occur per pupil each week. So the pivot has week number across the top row and then the names of individuals on the first column. The pivot will automatically update each time the spreadsheet is open in line with the additional form responses being recorded, so as the year goes on there will be additional week numbers added and additional incidents added.

From this pivot, I want to look at the mean number of incidents per pupil across the weeks, as well as the standard deviation. I originally thought I could make a pivot of the pivot but Excel doesn't recognise there being column titles on the pivot table. I cannot make a seperate pivot table from the raw data as it doesn't recognise the number of incidents per pupil per week and just looks at each incident as unlinked and individual so the mean comes up as 1 since it is looking at each row individually without any link.

So, I thought if I created a two dimensional dynamic named range over the pivot table output, I could use formulas looking at the named range so they would automatically update when additional data is added. However for some reason the dynamic range I've set up doesn't give the output I would expect from any simple formulas I use with it. I'm not totally confident using the INDEX/COUNT formulas and just followed an ableguides guide so I might be missing something obvious since I'm not super familiar with them. Originally in the pivot, blank values pulled through as a blank cell but to my understanding this would stop the formula working correctly as a blank cell would stop the count so I changed these to display a - instead.

The formula I have used for the two dimensional dynamic named range is:
Excel Formula:
='Phys Int'!$G$32:INDEX('Phys Int'!$1:$100,COUNT('Phys Int'!$G:$G),COUNT('Phys Int'!$32:$32))

The area I want to look at is (names anonymised):
1709112641269.png


The output from the named range I made using =SUM is 108, from =COUNT is 26 and from =AVERAGE is 4.2, where when not using a dynamic named range of the same area I would get =SUM of 88, =COUNT of 456 and =AVERAGE of 0.2 (although the true average excluding the - values should be 1.5).

I know I could manually update the calculated range weekly but I'm trying to avoid that if possible! I also know something similar could probably be done in PowerBI but that isn't an option.

Thank you so much in advance for any insight anyone can give!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
I think you'd be better off doing the calculations in Power Pivot.

Having said that, the reason your range is off is that you are indexing into row 1:100 based on the count of numbers in the whole of column G (and there are clearly blank rows and text in there), and the count of numbers in row 32 and again there are blanks in that row, so your data range is certainly not extending as far down and to the right as needed.
 
Upvote 0
I think you'd be better off doing the calculations in Power Pivot.

Having said that, the reason your range is off is that you are indexing into row 1:100 based on the count of numbers in the whole of column G (and there are clearly blank rows and text in there), and the count of numbers in row 32 and again there are blanks in that row, so your data range is certainly not extending as far down and to the right as needed.
Thank you - I was under the impression from the guide I used to set up the formula that the range needed to index the full scope of the spreadsheet but must have misunderstood.
1709125607253.png

I've never used power pivots before but will take a look.
 
Upvote 0
That would only apply if your data starts in row 1 column A and has no gaps in the first column and first row.
 
Upvote 0

Forum statistics

Threads
1,224,811
Messages
6,181,081
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top