Below is the link to an actual file I was emailed today:
Excel File: Scores Won’t Sum
This is a list of scores of test participants. Obviously all identifying information has been stripped from the file. I just want you to see that this is a real world example, not some silly made up sample.
This file was sent to me asking if I could return a pivot table showing the averages of all the grades. Note that not only are there multiple columns, but notice the ID field. One individual can represent multiple rows depending on how many courses they are taking.
Now figuring this out by hand (especially considering the original file had over 500 rows) would be a pain in the rear, but it should be no issue for a Pivot Table.
But look what happens when I try to average S1 against ID in a Pivot Table
So I go back to the main sheet and try performing a SUM function on the first Row. This results in 0 as an answer.
I try my hidden value trick: Numbers don’t add up in Excel
But still nothing. Then I remember about Paste Special
To fix the problem, go find an empty cell and type 1. Make sure to take note of the placement of your 1 in the cell. It should hug the right cell wall. If it justifies left, Excel is seeing it as text – try another cell.
Also check that your cell is marked as General in the drop down menu on the Number portion of the Ribbon bar.
Copy the cell with 1 in it. Highlight the columns you are having trouble with – right click and select Paste Special
Click Multiply and Okay – this multiplies all the highlighted cells by 1
Notice my Sum function now works
You will notice that empty fields are now populated with 0. This isn’t an issue with summation, but it will give you bad readings on an average. I chose to get rid of those using an If statement. I inserted a column and ran the following statement.
=if(C2=0,””,C2)
(if you need a primer on if statements – follow this link: Excel: =If() – Conditional Formulas )
This states that if C2 = 0 replace with an empty space “”, else just populate C2
After that is done, I copy my new column, and Paste Special – Values over the old column.
Now I can repeat for each column. When done, delete your inserted column.
Now my Pivot Table looks much better.
