software

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=quot;quot;,quot;quot;,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
with @tiscali.co.uk

quot;RBWquot; gt; wrote in message
...
gt; Using MS Excel to track the amount of oil we use, I'd like to create a
gt; more
gt; standardized way of looking at monthly usage. Deliveries of oil are
gt; somewhat
gt; random (Nov 21, Jan 3, Feb 27, etc.). I want to be able to create an
gt; average
gt; by month (for comparison to prior years). I can do this manually by
gt; looking
gt; at the number of days in the current month and in the prior month between
gt; oil
gt; deliveries, and then applying the daily average usage to each month, but
gt; this
gt; is highly manual and tedious. Is there a way to automate that allocation
gt; process to create more reliable monthly data?
gt;
gt; Thanks for your thoughts.
gt;
gt; Example: If we get 150 gallons on Mar 8, and the prior delivery was Feb
gt; 22,
gt; then average daily usage was 10.7 gallons. I can take 10.7, multiply by
gt; eight days in March and assign the result (85.7 gallons) to March and the
gt; rest of the delivery to February (64.3 gallons). While not perfect,
gt; especially if a delivery is close to the end or the beginning of a month,
gt; it
gt; does allow a more precise comparison to prior years.
Sandy,

Brilliant- works beautifully. Most impressive. Many thanks.

RBW

quot;Sandy Mannquot; wrote:

gt; I assume that when oil is delivered the tank is always fill fully up
gt; otherwise it will add another complication.
gt;
gt; With the dates in Row 1starting fromB1 and the deliveries in Row 2 then in
gt; C3 try:
gt;
gt; =IF(C1=quot;quot;,quot;quot;,ROUND(C2/(C1-B1)*DAY(C1) IF(ISNUMBER(D3),D2-D2/(D1-C1)*DAY(D1),0),0))
gt;
gt; However, like you said it is a rough and ready calculation assuming constant
gt; us of the oil which may well not be true.
gt;
gt; --
gt; HTH
gt;
gt; Sandy
gt;
gt; with @tiscali.co.uk
gt;
gt; quot;RBWquot; gt; wrote in message
gt; ...
gt; gt; Using MS Excel to track the amount of oil we use, I'd like to create a
gt; gt; more
gt; gt; standardized way of looking at monthly usage. Deliveries of oil are
gt; gt; somewhat
gt; gt; random (Nov 21, Jan 3, Feb 27, etc.). I want to be able to create an
gt; gt; average
gt; gt; by month (for comparison to prior years). I can do this manually by
gt; gt; looking
gt; gt; at the number of days in the current month and in the prior month between
gt; gt; oil
gt; gt; deliveries, and then applying the daily average usage to each month, but
gt; gt; this
gt; gt; is highly manual and tedious. Is there a way to automate that allocation
gt; gt; process to create more reliable monthly data?
gt; gt;
gt; gt; Thanks for your thoughts.
gt; gt;
gt; gt; Example: If we get 150 gallons on Mar 8, and the prior delivery was Feb
gt; gt; 22,
gt; gt; then average daily usage was 10.7 gallons. I can take 10.7, multiply by
gt; gt; eight days in March and assign the result (85.7 gallons) to March and the
gt; gt; rest of the delivery to February (64.3 gallons). While not perfect,
gt; gt; especially if a delivery is close to the end or the beginning of a month,
gt; gt; it
gt; gt; does allow a more precise comparison to prior years.
gt;
gt;
gt;

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

--
Regards

Sandy
with @tiscali.co.uk

quot;RBWquot; gt; wrote in message
...
gt; Sandy,
gt;
gt; Brilliant- works beautifully. Most impressive. Many thanks.
gt;
gt; RBW
gt;
gt; quot;Sandy Mannquot; wrote:
gt;
gt;gt; I assume that when oil is delivered the tank is always fill fully up
gt;gt; otherwise it will add another complication.
gt;gt;
gt;gt; With the dates in Row 1starting fromB1 and the deliveries in Row 2 then
gt;gt; in
gt;gt; C3 try:
gt;gt;
gt;gt; =IF(C1=quot;quot;,quot;quot;,ROUND(C2/(C1-B1)*DAY(C1) IF(ISNUMBER(D3),D2-D2/(D1-C1)*DAY(D1),0),0))
gt;gt;
gt;gt; However, like you said it is a rough and ready calculation assuming
gt;gt; constant
gt;gt; us of the oil which may well not be true.
gt;gt;
gt;gt; --
gt;gt; HTH
gt;gt;
gt;gt; Sandy
gt;gt;
gt;gt; with @tiscali.co.uk
gt;gt;
gt;gt; quot;RBWquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Using MS Excel to track the amount of oil we use, I'd like to create a
gt;gt; gt; more
gt;gt; gt; standardized way of looking at monthly usage. Deliveries of oil are
gt;gt; gt; somewhat
gt;gt; gt; random (Nov 21, Jan 3, Feb 27, etc.). I want to be able to create an
gt;gt; gt; average
gt;gt; gt; by month (for comparison to prior years). I can do this manually by
gt;gt; gt; looking
gt;gt; gt; at the number of days in the current month and in the prior month
gt;gt; gt; between
gt;gt; gt; oil
gt;gt; gt; deliveries, and then applying the daily average usage to each month,
gt;gt; gt; but
gt;gt; gt; this
gt;gt; gt; is highly manual and tedious. Is there a way to automate that
gt;gt; gt; allocation
gt;gt; gt; process to create more reliable monthly data?
gt;gt; gt;
gt;gt; gt; Thanks for your thoughts.
gt;gt; gt;
gt;gt; gt; Example: If we get 150 gallons on Mar 8, and the prior delivery was
gt;gt; gt; Feb
gt;gt; gt; 22,
gt;gt; gt; then average daily usage was 10.7 gallons. I can take 10.7, multiply
gt;gt; gt; by
gt;gt; gt; eight days in March and assign the result (85.7 gallons) to March and
gt;gt; gt; the
gt;gt; gt; rest of the delivery to February (64.3 gallons). While not perfect,
gt;gt; gt; especially if a delivery is close to the end or the beginning of a
gt;gt; gt; month,
gt;gt; gt; it
gt;gt; gt; does allow a more precise comparison to prior years.
gt;gt;
gt;gt;
gt;gt;