Apr 07 2020 09:48 AM - edited Apr 07 2020 02:18 PM
Greetings,
I am using a pivot table to compute the average of a list of whole number (ratings 1-5). The pivot table average is returning fractional values so rather than 5, returning 4.63 for example. I am wanting the average to be returned as a whole number.
Apr 07 2020 11:52 AM
@Cheryle2175 Difficult to follow what you are trying to achieve, but your formula seems to return the NUMBERVALUE of texts varying from "1", "2","3","4" and "5". Why not just skip the "numbervalue" and use the values 1, 2, 3, 4 and 5 in your formula to begin with?
Perhaps you can upload a sample of your workbook with the PT in it so that we can see how your formula affects the PT.
Apr 07 2020 02:07 PM
I over complicated the ask above. Here is the simple version: I am using a pivot table to compute the average of a list of whole number ratings 1-5. The pivot table average is returning fractional values so rather than 5, returning 4.63 for example.
Apr 07 2020 09:02 PM
@Cheryle2175I guess this is a matter of number formatting. Assuming that the ratings are in the Value area of the PT, and you have set the action to "Average" (i.e. not Sum or Count, for example). Here you can also change the number format. Set it to a number with 0 (zero) decimals. In your case, the real average is still 4.63 but it will show as 5.
Apr 08 2020 01:31 PM
@Riny_van_Eekelen Yes thank you I have updated the Average to zero decimal places. The table displays zero decimal places, the graph label displays zero decimal places. However, the actual graph of the value the bar illustrates multiple decimal place values. So for example two bars with label of "4" are different heights. See the attached example.
Apr 08 2020 09:09 PM
@Cheryle2175 Interesting problem, but I haven't been able to solve it within the pivot table/chart. Tried Calculated Fields but that didn't do what I had in mind. A work-around could be to create a "helper" table that picks-up values from the pivot table and rounds these. Then, you create your chart from that helper table with rounded values. The attached workbook contains an example. Not sure if this is feasible or acceptable for you, though.
Apr 09 2020 11:05 AM
@Riny_van_Eekelen Thanks, surprising their is not a way to control the average summarize value output.
Apr 09 2020 11:40 AM - last edited on Apr 09 2020 12:07 PM by Allen
Apr 09 2020 11:40 AM - last edited on Apr 09 2020 12:07 PM by Allen
No, you can achieve this.
What I got from your problem.
You are averaging the ratings, but its coming out to be in decimal. You want in a whole number.
If 2.4 then 2 if 4.6 then 5. If its the case . Just follow this
source:https://gyankosh.net/msexcel/how-to-use-pivot-tables-in-excel/#gsc.tab=0e
When you go to the field settings, the picture shown above comes up.
Just go to number format and choose one without the decimals.
Your problem is solved.
*If it was what I understood.