Fuel Calc problem

[mikeba]

Hello,

So I would like to have excel sheet calculate how much fuel is needed for an airplane sight seeing flight.
There is a 10 hour window for the trip to be completed. Any delay from scheduled departure time will result in a trip being shortened by the amount of time delayed.
If the flight is not delayed it will use 4000 lbs of fuel and fly over every sight seeing point.
The airplane burns 400 lbs of fuel per hour.
The airplane has 3 fuel tanks. Let's call them tanks A, B, and C. Tank A capacity is 2,000 lbs, tank B is 1,000 lbs, and tank C is also 1,000 lbs.
Based on time delay, I want to have excel show how much fuel is required for flight and how much fuel is required in each tank.
If a tank will not require fuel then I want the cell to read "empty", and if the tank is full to read "full", otherwise show fuel needed in that tank.
Tank A is filled first, then B tank, and finally C tank in that order.

Thanks for any suggestions on formulas.

Mike

 

[Jeffrey Mahoney]

In the two scenarios I layed out before, formulas in H:J rely on the values of each other. Meaning, Tank B needs to know how much fuel Tank A needs and Tank C needs to know how much fuel Tank B needs. If I change it to show "FULL", then it can't calculate correctly. My suggestion is to use conditional formatting to show full. In my example I colored the cells green if the tank is full.

Mikeba 20230718.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Pounds Per Hour:	400				Capacity:	2000	1000	1000
2
3	Flight Num	Planned Departure Date	Planned Departure Time	Actual Departure Time	Expected Flight Time	Actual Flight Time	Total Fuel Needed	Tank A	Tank B	Tank C
4	1234	7/19/2023	4:00	8:29	10.00	5.51	2,205.50	2,000.00	205.50	Empty
5	1235	7/19/2023	6:00	8:29	10.00	7.51	3,005.50	2,000.00	1,000.00	5.50
Sheet1

Cell Formulas
Range	Formula
F4:F5	F4	=(C4*24)+E4-(D4*24)
G4:G5	G4	=F4*PPH
H4:H5	H4	=MIN(G4,TankACap)
I4:I5	I4	=IF(MAX(MIN(G4-H4,TankBCap),0)=0,"Empty",MAX(MIN(G4-H4,TankBCap),0))
J4:J5	J4	=IF(MAX(MIN(G4-H4-I4,TankCCap),0)=0,"Empty",MAX(MIN(G4-H4-I4,TankCCap),0))
D4:D5	D4	=IF(B4=TODAY(),MAX(C4,NOW()-INT(NOW()),C4))

Named Ranges
Name	Refers To	Cells
PPH	=Sheet1!$C$1	G4:G5
TankACap	=Sheet1!$H$1	H4:H5
TankBCap	=Sheet1!$I$1	I4:I5
TankCCap	=Sheet1!$J$1	J4:J5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H5:J5	Expression	=H5=H$1	text	NO
H4:J4	Expression	=H4=H$1	text	NO

 

[mikeba]

Thanks Jeff!!!