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))
.
|