Calculating Weekly Average from Irregular Data

***I've bumped this and edited the first post to reflect the entire evolution of my thinking on this problem.***

I'm working with a locksmith shop to develop a schedule for purchases of key blanks. Based on recent and future blank purchases, I want to produce a running average of the blanks that are consumed.

Each model of key blank is purchased when its supply is exhausted. This happens at irregular intervals. Sometimes several models are purchased in an order and sometimes just one.

A sample spreadsheet, with just two blank models and four dates, is included. (Excel 2007.)

If I knew how to construct the formula, here's what it would do:

1) count the days between the first and last purchase, for a given key blank

2) sum all the purchases for that key blank, less the final purchase*

3) divide the sum from step 2 by the number of days from step 1, for a daily average

4) multiply the quotient from step 3 by 7 to get weekly usage (or 14 or 30)**

Thanks for any help you can provide!

*We omit the final purchase from the sum because without the next purchase date, we can't determine how quickly the new keys will be consumed. We use the date of final purchase as a proxy for when the second to last purchase was exhausted.

**This average could be weekly, biweekly, or monthly, depending on the multiplier that we plug in here. I'm not clear yet on what would be most useful, but I'm starting with weekly.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

What do you consider a week? A period of seven days starting on the first date? Form Sun to Sat, from Mon to Sun?

Thanks for all of your efforts.

My aim here is to calculate weekly consumption. Purchases on four consecutive Thursdays would likely be for four consecutive weeks, but we wouldn't actually know how quickly the fourth purchase would be used up until the next purchase was made. I see what you mean about them bookending a three-week stretch. Is there a way to construct the formula such that weekly usage is calculated based on all but the final purchase, but using the date of the final purchase as an end date? In other words, we'd be treating the purchases almost like right-censored spell data, since we don't know how long the materials last until they run out and we have to buy more.

In time, I suppose that the variance from the actual weekly average in the current formula would approach zero. So maybe this isn't a big issue.

More important at this point is that I be able to move the formula to column B so that my users never need to fiddle with adding columns in. I tried to do this myself but, alas, the formula was too much for me. Sorry I didn't think of this earlier.

And again, thank you for your time.

rodrignj,

The changes you've made are great. And I hate to nitpick because you're helping me out of the goodness of your heart, but I think the puzzle is not yet solved. Adding the +1 resolved the problem for the scenario I mentioned earlier, but given different entries, the problem remains. For example, if I have two purchases of 300 keys on Feb 28 and March 21 (three weeks later), it provides me with an average of 150. In fact, 300 keys used over 3 weeks should average out to 100.

I believe it's necessary to use the placeholder date rather than adding a week, as purchases are not usually made once a week. They could be made two days in a row and then two months apart from each other.

If I knew how to construct the formula, here's what it would do:

1) count the days between the first and last purchase, for a given key blank

2) sum all the purchases for that key blank, less the final purchase

3) divide the sum from step 2 by the number of days from step 1, for a daily average

4) multiply the quotient from step 3 by 7 to get weekly usage (or 14 or 30)

David

You're right about March 21 instead of March 31. I've corrected that in the original post.

I think you're misunderstanding the way I want to measure this, and I apologize for having been unclear up to this point. Also, I'm probably having trouble grasping what you're saying.

The assumption that I'm making is that new keys are purchased when we have used up all the old keys. And we can't use keys we've just purchased to measure how quickly we're using keys. We can only use purchased keys to measure key usage once they've been used up. We only know they've been used up because we had to buy new ones.

In the first example, we bought 300 keys on each of four consecutive Mondays (which, yes, bookended three weeks). In the first week, we used 300 keys, so we had to buy 300 more the next Monday. Same thing in the second week and in the third week. On the fourth Monday (three weeks after the first purchase), we bought 300 more keys. This means we're using keys at a rate of 300 per week. It could take us 10 weeks to use up the keys we bought on the 4th Monday. We don't know until they're gone.

In the second example, we bought 300 keys on a Monday. Three weeks later, we had to buy 300 more keys. That means that in the three weeks between these key purchases, we used 300 keys, or 100 keys per week.

I think you might be hung up on the idea that keys are purchased on a weekly basis, which they are not. In testing your formulas, I've used consecutive weeks because it makes it easier to see that the model is or isn't working correctly. But we can't make the assumption that key purchases will be regular or that they'll be exhausted within a week.

Thank you again, rodrignj, for your persistent help here.

robertandrews,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.