How to Set Excel to Establish When to Buy New Shoes

My question of the week was twofold:

How long before replacing shoes? And, how can I convince Excel to alert me that it is time to reorder? I forget to do things, so having a cheater helps.

I keep a lot of records in Excel. As it relates to this question, I have a spreadsheet with each day's mileage. Since buying my current shoes, I have run 215.40 miles.

Yesterday, Jim mentioned shoes are good for 350 miles. A rough figure, I'm sure, but as good as any, and, he's in the business to know. 350 it is.

350-215.40 = 134.60

I also know over the last four weeks, I have averaged 22.1525 miles a week.

134.60/22.1525 = 6.0760636497009366888613023360794 weeks before I blow a flat in my sneakers.

I'll say 6.1 weeks. It takes less typing.

The formula* is =(350-SUM(C:C))/22.1525

Copy that, paste it into a cell.

That is 350 miles minus the contents of column C (my total miles) divided by my weekly average. I'll change the average every few weeks, but it gets the gist of what I need to know. Around August 29, I need new shoes. I expect that to be a little earlier, but it gives me an idea when I should order.

To determine the amount of days, just change the formula to
=(350-SUM(C:C))/22.1525*7.

To have it determine the actual date, the formula is
=(TODAY()+(350-SUM(C:C))/22.1525*7)
(Be sure to change the cell format to date or else you'll get something which looks like: 39324.5324)

E. Litvin sums it all up for the runner whose mileage is fairly constant. As mine is still increasing, this is less useful, but, for the runner who is a steady Eddie, who knows week to week he's running more or less the same, this would work great, and requires no variable babysitting.

You can use the formula below to calculate days automatically. =TODAY()+(350-SUM(C:C))/(SUM(C:C)/COUNT(C:C)) Where: (350-SUM(C:C)) – calculates the miles remaining; COUNT(C:C) – counts the number of entries in (C:C); (SUM(C:C)/COUNT(C:C)) – calculates the average mileage for a day;

 

* A nod to those on the Amazon.com Reviewer's Discussion Board who helped me, particularly E. Litvin, Friederike Knabe, and C. McCallister.