ashleighbrown
New Member
- Joined
- Mar 10, 2023
- Messages
- 8
- Office Version
- 2016
- Platform
- 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:
The area I want to look at is (names anonymised):
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!
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):
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!
