Excel Formula
 

I work at a gas station and i am trying to figure out the average cost of
fuel in my tank. I have the current volumes and past deliveries set up below.

A2=13485 (current Volume)
A3=4565 (fuel delivery1)
A4=6554 (fuel delivery2)
A5=9004 (fuel delivery3)
A6=4815 (Fuel delivery4)

I need to know how much fuel from each delivery is in the tank. I know I
have all the fuel from deliveries 1 and 2 and 2366 from delivery 3. But how
do i get the formula to only count 2366 gallons from 3 and 0 from 4?

Excel Formula
 

Kelly -

Put this in cell B2 and drag it down the column.
=MAX(0,MIN(A$2-SUM(A$3:A3),A3))

This assumes you have kept the oldest deliveries in your tanks and used the
newest, as your description states.

The formula works like this:

We can never have a negative amount left from a delivery, hence the
Max(0,rest)
We can never have more in a delivery than what was delivered, or what is
left after we have accounted for in the prior deliveries, hence the Min(Amt
unaccounted for, delivery amt)
The amount unaccounted for is the current volume (A$2) less the amount from
the prior deliveries (Sum(A$3:A3)).

--
Daryl S


"Kelly" wrote:

I work at a gas station and i am trying to figure out the average cost of
fuel in my tank. I have the current volumes and past deliveries set up below.

A2=13485 (current Volume)
A3=4565 (fuel delivery1)
A4=6554 (fuel delivery2)
A5=9004 (fuel delivery3)
A6=4815 (Fuel delivery4)

I need to know how much fuel from each delivery is in the tank. I know I
have all the fuel from deliveries 1 and 2 and 2366 from delivery 3. But how
do i get the formula to only count 2366 gallons from 3 and 0 from 4?

Excel Formula
 

Daryl S wrote:
Kelly -

Put this in cell B2 and drag it down the column.
=MAX(0,MIN(A$2-SUM(A$3:A3),A3))

This assumes you have kept the oldest deliveries in your tanks and used the
newest, as your description states.

The formula works like this:

We can never have a negative amount left from a delivery, hence the
Max(0,rest)
We can never have more in a delivery than what was delivered, or what is
left after we have accounted for in the prior deliveries, hence the Min(Amt
unaccounted for, delivery amt)
The amount unaccounted for is the current volume (A$2) less the amount from
the prior deliveries (Sum(A$3:A3)).



I think you need this:

=MAX(0,MIN($A$2-SUM($A$3:A3)+A3,A3))

Excel Formula
 

How do i know how much fuel is in the tank from each delivery? Each delivery
has a different cost.

"Daryl S" wrote:

Kelly -

Put this in cell B2 and drag it down the column.
=MAX(0,MIN(A$2-SUM(A$3:A3),A3))

This assumes you have kept the oldest deliveries in your tanks and used the
newest, as your description states.

The formula works like this:

We can never have a negative amount left from a delivery, hence the
Max(0,rest)
We can never have more in a delivery than what was delivered, or what is
left after we have accounted for in the prior deliveries, hence the Min(Amt
unaccounted for, delivery amt)
The amount unaccounted for is the current volume (A$2) less the amount from
the prior deliveries (Sum(A$3:A3)).

--
Daryl S


"Kelly" wrote:

I work at a gas station and i am trying to figure out the average cost of
fuel in my tank. I have the current volumes and past deliveries set up below.

A2=13485 (current Volume)
A3=4565 (fuel delivery1)
A4=6554 (fuel delivery2)
A5=9004 (fuel delivery3)
A6=4815 (Fuel delivery4)

I need to know how much fuel from each delivery is in the tank. I know I
have all the fuel from deliveries 1 and 2 and 2366 from delivery 3. But how
do i get the formula to only count 2366 gallons from 3 and 0 from 4?

Excel Formula
 

Thanks, This works great

"Glenn" wrote:

Daryl S wrote:
Kelly -

Put this in cell B2 and drag it down the column.
=MAX(0,MIN(A$2-SUM(A$3:A3),A3))

This assumes you have kept the oldest deliveries in your tanks and used the
newest, as your description states.

The formula works like this:

We can never have a negative amount left from a delivery, hence the
Max(0,rest)
We can never have more in a delivery than what was delivered, or what is
left after we have accounted for in the prior deliveries, hence the Min(Amt
unaccounted for, delivery amt)
The amount unaccounted for is the current volume (A$2) less the amount from
the prior deliveries (Sum(A$3:A3)).



I think you need this:

=MAX(0,MIN($A$2-SUM($A$3:A3)+A3,A3))
.