Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)) . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
i edit a formula (excel) then it displays formula not answer | Excel Discussion (Misc queries) |