Grouping daily data into monthly. three columns equal fourth. wanting to know what percentage column 1 is on a daily and monthly, then yearly total. I did it yesterday but now do not know where I am going wrong.
Example
6, 7, 1, = 14
6 = 42.86% but the pivot table does 600% when I do Average and show as percentage. Same with 7 and 1.
Hi,
It will display 600% because the underlying number is 6. If you wish to show the 42.8%, 50% and 7.14% then you'll either need to include those columns in the Pivot Table definition or in the PT use the Calculated Field option and add a field with the calculation 'No Show / Total'...ditto for the other two.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Sorry, but that doesn't work either. I attached a screenshot of what happens when I include those columns. I also attached a screenshot of what I did yesterday but can't seem to replicate. But I know I did not do anything to the calculated field.
Does the attached help?
I've added three calculated fields
Hi,
I tried using calculated fields first but the resulting averages didn't match what is now shown on sheet 1.
I then added the %age columns (as per Richard's post #2) and the averages matched with what is now shown on Sheet 1. Please see attached.
What I don't understand is why is there a difference, what is causing this - am I missing something?
Regards
peterrc
Hi,
You say you tried adding calculated fields but they are not evident in the workbook you posted. Please upload the workbook in which you have added the calculate field. Unless we can see what you've done we can't really say why you don't get the %s from calculated fields.
Check the workbook I showed you and observe for instance how the 'NoShow%' calculated field is defined as '='No Show'/Total'
Hi,
@ Richard
I was hoping you would reply because I don't know why there is a difference.
I have attached my original Pivot Table (using calculaed fields) alongside my earlier post #5, see attached.
You can see the differences and I am wondering if it is simply a "round-up / round-down issue".
What is interesting is that this additional Pivot Table "matches" your Pivot Table in post #4.
What are your thoughts?
Regards
peterrc
It's because you are not comparing like with like.
Averaging averages is a mistake that is commonly made and is arithmetically incorrect.
Your calculated %Noshow of 33.74% is derived by averaging the individual averages in F2:F23. But this takes no account of the weighting of the number of no shows. If you want an average for a series of numbers then you need to work with the overall totals that are included in calculating your average percentages. That's what the calculated field correctly does.
The correct average is the sum of B2:B23 = 128, divided by the sum of E2:E23 = 387. Hence 128/387 = 33.07%
Thank you Richard - I cannot fault you logic.
However (lol),
If you highlight all the percentages in red on Sheet1, Excel shows the Average on the bottom "bar" as 33.74%, which is where I got the figure from (this doesn't strike me as "Averaging averages").
And as you showed, using the formula =SUM(B2:B23)/SUM($E$2:$E$23) it shows 33.07%.
Weird!
Regards
peterrc
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks