Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question

migrated from superuser.com Jun 22 '13 at 15:30

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

2 Answers 2

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%
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.