Spreadsheet to calculate weekly increase

I'm trying to create a spreadsheet with my weekly cardio and weight training average times and a total of time spent during an average visit.

I would like the final column to display the in/decrease as a percentage since the last week. Then, build a line graph that displays the upward trend.

Please know, I'm no math/spreadsheet genius. This is it so far. I read some of the docs but just got lost in them.

migrated from superuser.comJun 22 '13 at 15:30

This question came from our site for computer enthusiasts and power users.

Your calculation of "This time" / "Last time" expressed as a percentage (by the formatting) is essentially correct, except it isn't working out the increase.

So if it was 10 minutes yesterday, and 10 minutes today, that means you did 100% of the time you did yesterday. This is what you are calculating now.

So (Today / Yesterday) - 1 will give you the increase (when expressed as a percentage). The subtracting 1 removes the original amount.

For example, if it was 10 minutes yesterday, and 11 minutes today, then that is

11 / 10 = 1.1
1.1 - 1 = .1
.1 * 100 = 10%

If you did 9 minutes today and ten yesterday:

9 / 10 = .9 - 1 = -.1 * 100 = -10%

I suggest replacing =DIVIDE(D2 ; D3) in E3 with =(D3-D2)/D2 and formatting ColumnE as percentage. This will give #DIV/0! errors in some cases because there is no meaningful percentage increase to be calculated where the base is zero.

For your chart, select your data in ColumnD (includingD1), Insert, Chart, Charts, Line, click on the top image and Insert. Hopefully most of the rest will be automatic.