Pivot Table Results Are Huge

Douglas Eckert

New Member
Joined
May 26, 2011
Messages
7
Pivot table results (using SUM) are huge - not the numbers displayed in the underlying table. If I use COUNT, they are too small. Please advise.

DOUG in Wichita KS
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,121
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Need a bit more to go on than that. PT results are correct as a rule, even if they are not what you expect.
 

Douglas Eckert

New Member
Joined
May 26, 2011
Messages
7
The results which appear in the pivot table are not the numbers which are found in the source table. They should be displaying scores in the 90 - 100 percent range. Instead, they are displayed as 1800 plus in the pivot table. I have used pivot tables for years and I have never experienced this. Maybe I am having writer's block today or something. I have a slide to show you, but it is on my PC and not posted at a URL...
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,121
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
If you double-click one of the values to bring up the detail sheet, are the numbers there correct?
 

Douglas Eckert

New Member
Joined
May 26, 2011
Messages
7
Expected results would have been in the range of 90 to 100 percent, i.e., matching the scores in the source table. But, they are on the order of 1800 plus when I use SUM in the Field Settings. If I use COUNT, all of the results are the number 21. I have used pivot tables successfully for many years, but I have never experienced this problem. I am trying to display metric scores in a pivot table. The table is fine. Clinic names are displayed as row headings and months are displayed as column headings. But, the numbers are all wrong.






:confused:douglas.eckert.1@us.af.mil
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,121
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
If you have 21 entries in the 90-100% range and you sum them, you will get something in the 1800%+ range, since all it does is add up the percentages.
 

Douglas Eckert

New Member
Joined
May 26, 2011
Messages
7
I guess I need to know why it is multiplying these items together instead of just displaying the scores (?)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,121
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
It's not multiplying, it's adding. If you have 21 values of 0.9, then the total is 18.9 or 1890%. What are you expecting it to return? (if you want an average, use that, but it won't be weighted, if that's important)
 

Douglas Eckert

New Member
Joined
May 26, 2011
Messages
7
I am expecting it to produce a neat little table like it has in the past. Column A would contain the names of clinics in our company, field headings would be the months and monthly scores would be displayed in the columns. I have used the SUM option before to produce this kind of pivot table, although I was not really summing anything, just displaying the data. On another related website I was just looking at a tutorial which showed an example of the same thing I am trying to do, using the same steps. (I wanted to send you the link, but the page has closed now). It shows the academic grades for two students in the source table. The pivot table uses their names as a filter. The names of their classes are in column A, the months are the field headings atop the columns. The columns display their grades. The grade scores are not being added or acted upon, just displayed in the pivot table. I am attempting to do the same thing using metric scores.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,121
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
It would be helpful to see your data layout. If you are getting a count of 21 for each student, then it appears you have 21 items being aggregated. On that basis, you cannot simply display the scores. Why do you have 21 items per student?
 

Watch MrExcel Video

Forum statistics

Threads
1,102,274
Messages
5,485,783
Members
407,515
Latest member
franjey

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top