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]

Does this get you close to your goal?

Book2
	A	B	C	D	E	F	G	H	I	J
1		Gallons 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/18/2023	7:00	7:25	10:00	9:34	3,830.27	2,000.00	1,000.00	830.27
Sheet1

Cell Formulas
Range	Formula
D4	D4	=IF(B4=TODAY(),MAX(C4,NOW()-INT(NOW()),C4))
F4	F4	=C4+E4-D4
G4	G4	=F4*24*GPH
H4	H4	=MIN(G4,TankACap)
I4	I4	=MIN(G4-H4,TankBCap)
J4	J4	=MIN(G4-H4-I4,TankCCap)

Named Ranges
Name	Refers To	Cells
GPH	=Sheet1!$C$1	G4
TankACap	=Sheet1!$H$1	H4
TankBCap	=Sheet1!$I$1	I4
TankCCap	=Sheet1!$J$1	J4

 

[Jeffrey Mahoney]

I changed a couple cells. I forgot to account that you needed Empty put in the tank cells if 0 fuel was needed for that tank

Book2
	H	I	J
3	Tank A	Tank B	Tank C
4	2,000.00	1,000.00	803.15
Sheet1

Cell Formulas
Range	Formula
H4	H4	=MIN(G4,TankACap)
I4	I4	=IF(MAX(MIN(G4-H4,TankBCap),0)=0,"Empty",MAX(MIN(G4-H4,TankBCap),0))
J4	J4	=IF(MAX(MIN(G4-H4-I4,TankCCap),0)=0,"Empty",MAX(MIN(G4-H4-I4,TankCCap),0))

Named Ranges
Name	Refers To	Cells
TankACap	=Sheet1!$H$1	H4
TankBCap	=Sheet1!$I$1	I4
TankCCap	=Sheet1!$J$1	J4

 

[Jeffrey Mahoney]

Sorry for the iterations. I changed the cell format for Expected Flight Time and Actual Flight Time to be a decimal number instead of Time. It's easier to enter and understand. This is a complete redo.

Book2
	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/18/2023	4:00	7:37	10.00	6.37	2,549.81	2,000.00	549.81	Empty
Sheet1

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

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

 

[rlv01]

Tank A is filled first, then B tank, and finally C tank in that order.

That seems odd. The capacities suggest that tanks B & C are wing tanks. Don't you want the takeoff weight balanced between them as much as possible?

 

[Fluff]

Don't you want the takeoff weight balanced between them as much as possible?

In reality yes, but this sounds like a homework question.

 

[rlv01]

In reality yes, but this sounds like a homework question.

I assumed so, but if we are going to supply solutions to homework problems, why not encourage some thinking along the way? .

Just for fun:

Book4
	A	B	C	D
1	Plane Data	Tank A	Tank B
2	Main Fuel Tank A Capacity	2500	lbs
3	Wing Tank B Capacity	1000	lbs
4	Wing Tank C Capacity	1000	lbs	(wing tanks are the same size)
5	Total Onboard Fuel Capacity	4500	lbs
6
7	Fuel Consumption Rate	400	lbs/hour
8
9	Trip Data
10	Normal Flight Duration	10	hours
11	Flight Duration Safety Margin	0.5	hours
12	Normal flight fuel requirement	4200	lbs
13
14	Trip Delay Data
15	Flight delay	1.5	hours
16	Flight duration if delayed	8.5	hours
17	Delayed flight fuel requirement	3600	lbs
18
19	Tank Fill Requirements	Tank Fill
20	Tank A	2500	lbs
21	Tank B	550	lbs
22	Tank C	550	lbs
23	Calc Check:	3600	lbs
Sheet2

Cell Formulas
Range	Formula
B4,B22	B4	=B3
B5,B23	B5	=SUM(B2:B4)
B12	B12	=(B10+B11)*B7
D12	D12	=IF(B12>B5,"Warning - Flight fuel requirement exceeds fuel capacity!","")
B16	B16	=IF(B15>B10,0,B10-B15)
B17	B17	=(B16+B11)*B7
B20	B20	=IF($B$17>=B2,B2,$B$17)
B21	B21	=IF(($B$17-B20)/2<=0,"Empty",($B$17-B20)/2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D12	Expression	=$B$12>$B$5	text	NO
B12	Expression	=$B$12>$B$5	text	NO
B23	Expression	=$B$17=$B$23	text	NO
B21:B22	Cell Value	="Empty"	text	NO

 

[mikeba]

I assumed so, but if we are going to supply solutions to homework problems, why not encourage some thinking along the way? .

Just for fun:

Book4
	A	B	C	D
1	Plane Data	Tank A	Tank B
2	Main Fuel Tank A Capacity	2500	lbs
3	Wing Tank B Capacity	1000	lbs
4	Wing Tank C Capacity	1000	lbs	(wing tanks are the same size)
5	Total Onboard Fuel Capacity	4500	lbs
6
7	Fuel Consumption Rate	400	lbs/hour
8
9	Trip Data
10	Normal Flight Duration	10	hours
11	Flight Duration Safety Margin	0.5	hours
12	Normal flight fuel requirement	4200	lbs
13
14	Trip Delay Data
15	Flight delay	1.5	hours
16	Flight duration if delayed	8.5	hours
17	Delayed flight fuel requirement	3600	lbs
18
19	Tank Fill Requirements	Tank Fill
20	Tank A	2500	lbs
21	Tank B	550	lbs
22	Tank C	550	lbs
23	Calc Check:	3600	lbs
Sheet2

Cell Formulas
Range	Formula
B4,B22	B4	=B3
B5,B23	B5	=SUM(B2:B4)
B12	B12	=(B10+B11)*B7
D12	D12	=IF(B12>B5,"Warning - Flight fuel requirement exceeds fuel capacity!","")
B16	B16	=IF(B15>B10,0,B10-B15)
B17	B17	=(B16+B11)*B7
B20	B20	=IF($B$17>=B2,B2,$B$17)
B21	B21	=IF(($B$17-B20)/2<=0,"Empty",($B$17-B20)/2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D12	Expression	=$B$12>$B$5	text	NO
B12	Expression	=$B$12>$B$5	text	NO
B23	Expression	=$B$17=$B$23	text	NO
B21:B22	Cell Value	="Empty"	text	NO

Thanks for all the suggestions. I think this will work great!!

 

[mikeba]

Does this get you close to your goal?

Book2
	A	B	C	D	E	F	G	H	I	J
1		Gallons 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/18/2023	7:00	7:25	10:00	9:34	3,830.27	2,000.00	1,000.00	830.27
Sheet1

Cell Formulas
Range	Formula
D4	D4	=IF(B4=TODAY(),MAX(C4,NOW()-INT(NOW()),C4))
F4	F4	=C4+E4-D4
G4	G4	=F4*24*GPH
H4	H4	=MIN(G4,TankACap)
I4	I4	=MIN(G4-H4,TankBCap)
J4	J4	=MIN(G4-H4-I4,TankCCap)

Named Ranges
Name	Refers To	Cells
GPH	=Sheet1!$C$1	G4
TankACap	=Sheet1!$H$1	H4
TankBCap	=Sheet1!$I$1	I4
TankCCap	=Sheet1!$J$1	J4

 

[mikeba]

Jeff,

I attached screenshots of my spreadsheet. Just need to tweak formulas. So if total fuel required is > or = 2000, Tank A should read "full". If less than 2000, Tank A should read fuel required amount in cell C12. Tank B and C at this point should read "Empty". Tank A has to be full first, then tank B, followed by Tank C. If fuel required is between 2000 and 3,000 then Tank A would read "Full", and Tank B would show the fuel difference between 2000 and 3000. Tank C would read "empty". If fuel required is between 3000 and 4000 then Tanks A and B would read "full" and the difference between 3000 and 4000 would show in Tank C. If fuel required is 4000 then all Tanks would read "Full".

Thanks for your input.

Mike