Payload calculator formulas

Hello,
I'm working on building a payload calculator for a small fleet of single engine airplanes. I got stuck on 1 part and am wondering if someone is able to help me out.

In the attached excel sheet you'll see that on sheet 1 the airplane and pilot is selected. Then a departure and destination is selected in the drop down list for each leg (up to 5 legs). The idea is that once that is filled in the payload is automatically calculated.

Column "I" shows the fuel burn for that leg. In the tabs "DistHdgMin GA8" and "DistHdgMin C206" each leg shows a time(in minutes), heading and distance for this calculation I only need the time as the airplanes burn 1liter/minute. Is there a way to atomically put the number of minutes into column I depending on the selected route?

I would appreciate any help!

What determines which table to use?

Good point! The airplane PZ-NMM uses the C206 table. Both the PZ-NMA and PZ-NMF use the GA8 table.

Originally Posted by JohnTopley

What determines which table to use?

Try

=INDEX('DistHdgMin GA8'!$D$9:$AL$115,MATCH(Sheet1!$D8,'DistHdgMin GA8'!$A$9:$A$115,0)+2,MATCH(Sheet1!$E8,'DistHdgMin GA8'!$D$6:$AL$6,0))

BUT we need to know which tab and by default select the "Min" row.

Updated ..


=IF($B$3="PZ-NMM",INDEX('DistHdgMin C206'!$D$9:$AL$115,MATCH(Sheet1!$D8,'DistHdgMin C206'!$A$9:$A$115,0)+2,MATCH(Sheet1!$E8,'DistHdgMin C206'!$D$6:$AL$6,0)),INDEX('DistHdgMin GA8'!$D$9:$AL$115,MATCH(Sheet1!$D8,'DistHdgMin GA8'!$A$9:$A$115,0)+2,MATCH(Sheet1!$E8,'DistHdgMin GA8'!$D$6:$AL$6,0)))

When I try to put that in cell I8 it gives me an error message, any idea what that could be?

See attached.

That works perfect thank you!

That leaves me with one question regarding a different formula. Each airstrip has a maximum take-off weight, these values are listed in the tab "lists" under take off restrictions.

When the airplane PZ-NMM is selected the T/O restriction under C206 should be used, for PZ-NMA and PZ-NMF the restrictions under GA8.

On the first tab in cell F8 the following formula is put in: =(H8+(0.72*I8))-(J8*0.72)-K8-L8. The part =(H8+(0.72*I8) should however be maximized to the value listed in the "lists" tab according to the departure airstrip (and airplane) that has been selected on each leg.

Is this possible? Your help will be greatly appreciated again!

If I interpret the requirement correctly:

in F8

=MIN(($H8+(0.72*$I8)),VLOOKUP($D$8,Lists!$D$3:$F$37,IF($B$3="PZ-NMM",2,3),0))-(J8*0.72)-K8-L8

i.e ($H8+(0.72*$I8) must <= value in "LIst"

Perfect, thank you!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.