DaveC426913
Gold Member
- 23,830
- 7,815
- TL;DR Summary
- I have numbers for orders placed approximately every three weeks; I want to convert that to months
A simple spreadsheet to work out cost of consumption.
DoP (raw): I've placed seven orders from Feb to Jun.
Amount (raw): Depending on the specific product, sometimes it comes in bundles of 20, sometimes in bundles of 25.
Cost (raw): I've listed the cost for each order.
Duration (derived): How many days since the last order.
Rate (derived): Number consumed / Duration (So, 20 were ordered on Feb 24, and they lasted 20 days, i.e. 1 per day)
Cost/day (derived): Number consumed / cost of order ($158/20= $7.90 per day)
I've got the averages worked out. Even though there's seven orders, I can't count them all because either I don't have the consumption rates for February, or I don't have the consumption for June yet.
Where I'm stumped is C12: converting the cost to a monthly average, since so far, it's all based on orders approximately 18.5 days apart.
I am calculating the total cost and dividing by how many days have passed. (=SUM(C2:C8) / (DATEDIF(A2:A8,"d")/30) )
But $280/month is too high.
My sanity check says it should be approximately $250. I get this by taking the average cost per order ($152 (C11)) and dividing by .6 (total duration/30), which will give me approximately $250 per month.
What have I missed? I must not be including the full range of dates (DATEDIF(A2,A8,"d")/30), or maybe too many orders are included (C2:C8).
DoP (raw): I've placed seven orders from Feb to Jun.
Amount (raw): Depending on the specific product, sometimes it comes in bundles of 20, sometimes in bundles of 25.
Cost (raw): I've listed the cost for each order.
Duration (derived): How many days since the last order.
Rate (derived): Number consumed / Duration (So, 20 were ordered on Feb 24, and they lasted 20 days, i.e. 1 per day)
Cost/day (derived): Number consumed / cost of order ($158/20= $7.90 per day)
I've got the averages worked out. Even though there's seven orders, I can't count them all because either I don't have the consumption rates for February, or I don't have the consumption for June yet.
Where I'm stumped is C12: converting the cost to a monthly average, since so far, it's all based on orders approximately 18.5 days apart.
I am calculating the total cost and dividing by how many days have passed. (=SUM(C2:C8) / (DATEDIF(A2:A8,"d")/30) )
But $280/month is too high.
My sanity check says it should be approximately $250. I get this by taking the average cost per order ($152 (C11)) and dividing by .6 (total duration/30), which will give me approximately $250 per month.
What have I missed? I must not be including the full range of dates (DATEDIF(A2,A8,"d")/30), or maybe too many orders are included (C2:C8).