General Category > OT Knowledge

Excel formula (sort of off-topic but bike related)

(1/2) > >>

robgul:
A question for the Excel experts please .....

. . . as part of my ongoing return to better health I record all mileage on my bikes ... using a simple Excel spreadsheet with date, rough description of ride, distance and which bike ridden - that's all pretty straightforward and I have monthly totals and a running annual total total.   I also have an average "miles per week" calculation based on a simple  "miles to date divided by 52 weeks" formula - i.e. the average I would achieve for a whole year which will change as the year progresses.

What I'd really like to do is to have an "average miles per day for the year so far"    - in other words the running total divided by the number of days (since 1 January) that have elapsed this year.

I am assuming that there is a way to create a formula that tells me the "day number" based on the latest ride date which I can divide into the running total.

My Excel training was undertaken in 1992 - and Excel's help has so much jargon that I just fazed over - hence asking the question! 

Thanks

Rob

David Martin:
if cell A1 contians your start of year as 1/1/2016 and is a date then you can do =days(now()-A1) or similar to get the number of days since the start of the year.

robgul:

--- Quote from: David Martin on 23 July, 2016, 08:20:09 am ---if cell A1 contians your start of year as 1/1/2016 and is a date then you can do =days(now()-A1) or similar to get the number of days since the start of the year.

--- End quote ---

Hmm, that doesn't seem to work ....

My spreadsheet has the start of the year date in cell A1 and a (dynamic) today's date in cell A104 - trying various formulae gives me either a date in July 2132 or an infinite cell full of hash symbols??

Rob

Deano:
Try =TODAY()-A1

robgul:

--- Quote from: Deano on 23 July, 2016, 09:03:51 am ---Try =TODAY()-A1

--- End quote ---

... yep that works, cracked it while you were posting - today is the 204th day of the year and my daily average is a poor 10.3  :-[  (but that does take into account some very poor weather, hospital appointments that clashed with weekly club runs and 3 weeks of holidays without a bike)

Target at the start of the year was to get to about 12.5 per day an average (i.e. about 4,500 for the year)

Rob

Navigation

[0] Message Index

[#] Next page