Excel: Convert Numbers from Text

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.

grades

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

grades1.jpg

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.

grades2.jpg

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.

grades3

Copy the cell with 1 in it. Highlight the columns you are having trouble with – right click and select Paste Special

grades4.jpg

Click Multiply and Okay – this multiplies all the highlighted cells by 1

grades5.jpg

Notice my Sum function now works

grades6

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.

grades7.jpg

Now my Pivot Table looks much better.

grades8

 

 

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s