Fuel Calc problem

mikeba

New Member
Joined
Jul 18, 2023
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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
 
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
ABCDEFGHIJ
1Pounds Per Hour:400Capacity:200010001000
2
3Flight NumPlanned Departure DatePlanned Departure TimeActual Departure TimeExpected Flight TimeActual Flight TimeTotal Fuel NeededTank ATank BTank C
412347/19/20234:008:2910.005.512,205.502,000.00205.50Empty
512357/19/20236:008:2910.007.513,005.502,000.001,000.005.50
Sheet1
Cell Formulas
RangeFormula
F4:F5F4=(C4*24)+E4-(D4*24)
G4:G5G4=F4*PPH
H4:H5H4=MIN(G4,TankACap)
I4:I5I4=IF(MAX(MIN(G4-H4,TankBCap),0)=0,"Empty",MAX(MIN(G4-H4,TankBCap),0))
J4:J5J4=IF(MAX(MIN(G4-H4-I4,TankCCap),0)=0,"Empty",MAX(MIN(G4-H4-I4,TankCCap),0))
D4:D5D4=IF(B4=TODAY(),MAX(C4,NOW()-INT(NOW()),C4))
Named Ranges
NameRefers ToCells
PPH=Sheet1!$C$1G4:G5
TankACap=Sheet1!$H$1H4:H5
TankBCap=Sheet1!$I$1I4:I5
TankCCap=Sheet1!$J$1J4:J5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
H5:J5Expression=H5=H$1textNO
H4:J4Expression=H4=H$1textNO
 
Upvote 0
Solution

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top