This is the Spreadsheet excerpt for the Documenting a Day project. Check out the capstone post, where I talk about the overall results. A Spreadsheet was provided with a list of 10 activities.
The first task was to fill out how many hours were spent on each activity. Once each entry was filled out, I sorted the table by hours from to least.
| B | C | D | |
| 2 | Activity | Hours | % |
| 3 | Office | 10 | |
| 4 | Sleep | 7 | |
| 5 | Other | 2.75 | |
| 6 | Homework | 2 | |
| 7 | Family time | 1 | |
| 8 | Video Games | 0.5 | |
| 9 | Watch TV | 0.5 | |
| 10 | Read | 0.25 | |
| 11 | Play Guitar | 0 | |
| 12 | Gym | 0 | |
| 13 | TOTAL |
After filling out the hours, the next task was to populate relevant cells with formulas to calculate totals and percent of totals.
| B | C | D | |
| 2 | Activity | Hours | % |
| 3 | Office | 10 | =C3/$C$13 |
| 4 | Sleep | 7 | =C4/$C$13 |
| 5 | Other | 2.75 | =C5/$C$13 |
| 6 | Homework | 2 | =C6/$C$13 |
| 7 | Family time | 1 | =C7/$C$13 |
| 8 | Video Games | 0.5 | =C8/$C$13 |
| 9 | Watch TV | 0.5 | =C9/$C$13 |
| 10 | Read | 0.25 | =C10/$C$13 |
| 11 | Play Guitar | 0 | =C11/$C$13 |
| 12 | Gym | 0 | =C12/$C$13 |
| 13 | TOTAL | =SUM(C3:C12) | =SUM(D3:D12) |
I started by getting a grand total of the number of hours in C13. This was useful in making sure I account for all 24 hours of my day and is used in future calculations. Next, I needed to calculate each activity's percent of the day in the D column. This is done by dividing the entry's hours by the total. The Hours entry for Office is C3, and the total is C13, giving me "=C3/C13". You'll note that I used "$C$13" instead of just "C13" though. This is an absolute reference so that I can copy the formula from D3 done one into D4, and the reference to total will stay at the C13 cell, but the reference to C3 will move down one to C4. I also put a grand total formula in D13 to again verify that my totals came to the expected number, in this case, 100%.
| B | C | D | |
| 2 | Activity | Hours | % |
| 3 | Office | 10 | 41.67% |
| 4 | Sleep | 7 | 29.17% |
| 5 | Other | 2.75 | 11.46% |
| 6 | Homework | 2 | 8.33% |
| 7 | Family time | 1 | 4.17% |
| 8 | Video Games | 0.5 | 2.08% |
| 9 | Watch TV | 0.5 | 2.08% |
| 10 | Read | 0.25 | 1.04% |
| 11 | Play Guitar | 0 | 0.00% |
| 12 | Gym | 0 | 0.00% |
| 13 | TOTAL | 24 | 100.00% |
The last task was to insert a pie chart of the results using the program's built-in graphing capabilities.
The Spreadsheet program used was Microsft Excel. If you try these formulas in a different program, your results may vary.
No comments:
Post a Comment