Convert irregular data to monthly equivalent?

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.

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
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

"RBW" <RBW@discussions.microsoft.com> wrote in message
news:E2FAB3CD-F043-47EF-B385-75D35D76EA06@microsoft.com...
> 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,

Brilliant- works beautifully. Most impressive. Many thanks.

RBW

"Sandy Mann" wrote:

> 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
> sandymann2@mailinator.com
> Replace@mailinator.com with @tiscali.co.uk
>
> "RBW" <RBW@discussions.microsoft.com> wrote in message
> news:E2FAB3CD-F043-47EF-B385-75D35D76EA06@microsoft.com...
> > 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.
>
>
>

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

--
Regards

Sandy
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk

"RBW" <RBW@discussions.microsoft.com> wrote in message
news:13FE58B0-47C5-4755-9FE6-3772CC6D1A5B@microsoft.com...
> Sandy,
>
> Brilliant- works beautifully. Most impressive. Many thanks.
>
> RBW
>
> "Sandy Mann" wrote:
>
>> 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
>> sandymann2@mailinator.com
>> Replace@mailinator.com with @tiscali.co.uk
>>
>> "RBW" <RBW@discussions.microsoft.com> wrote in message
>> news:E2FAB3CD-F043-47EF-B385-75D35D76EA06@microsoft.com...
>> > 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.
>>
>>
>>