Convert irregular data to monthly equivalent?

[Guest]

Using MS Excel to track the amount of oil we use, I'd like to create a more
standardized way of looking at monthly usage. Deliveries of oil are somewhat
random (Nov 21, Jan 3, Feb 27, etc.). I want to be able to create an average
by month (for comparison to prior years). I can do this manually by looking
at the number of days in the current month and in the prior month between oil
deliveries, and then applying the daily average usage to each month, but this
is highly manual and tedious. Is there a way to automate that allocation
process to create more reliable monthly data?

Thanks for your thoughts.

Example: If we get 150 gallons on Mar 8, and the prior delivery was Feb 22,
then average daily usage was 10.7 gallons. I can take 10.7, multiply by
eight days in March and assign the result (85.7 gallons) to March and the
rest of the delivery to February (64.3 gallons). While not perfect,
especially if a delivery is close to the end or the beginning of a month, it
does allow a more precise comparison to prior years.

 

[Sandy Mann]

I assume that when oil is delivered the tank is always fill fully up
otherwise it will add another complication.

With the dates in Row 1starting fromB1 and the deliveries in Row 2 then in
C3 try:

=IF(C1="","",ROUND(C2/(C1-B1)*DAY(C1)+IF(ISNUMBER(D3),D2-D2/(D1-C1)*DAY(D1),0),0))

However, like you said it is a rough and ready calculation assuming constant
us of the oil which may well not be true.

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

Sandy,

Brilliant- works beautifully. Most impressive. Many thanks.

RBW

 

[Sandy Mann]

Just glad that it does what you wanted. Thanks for the feedback

--
Regards

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk