Symptoms:
I was working in Excel spreadsheet that was a downloaded csv sheet. I would put in a formula to sum a column and I would get this:
A |
40 |
60 |
20 |
=sum(a1:a3) |
The formula would not work. I changed it to average and still you only would get the text. The number
Gathering Information
I looked at the sheet and found that all the cell where formatted as text. This was because the sheet was an imported comma separated download of grades. Number are generally set flush right but the text was flush left.
Hypothesis
H1 Convert the cell format to numbers general. (Failed)
H2 Use error correct to covert text to numbers. (Accepted)
Results
The number stored as texts can be corrected using the excel error correcting utility. The cells have a green triangle in the upper left corner. See the video in previous post. You click on the diamond shaped and you get a dropped menu. Select convert to number.
See the movie in the previous steps to see the hands on instruction.
Reflection
Exporting data is tricky. This is a very simple example but imagine a whole school system's data needing adjustments.
No comments:
Post a Comment