Your help is badly needed to solve the below

N

NADERASSALEH

New Member
Joined
Feb 12, 2014
Messages
27
DEARS,
iam trying to write a formula to give me fuel figures' effect or index based on the the amount of fuel onboard and fuel density and aircraft type. i could write a formula but it give fuel index for one type of density

=VLOOKUP($C$7,CHOOSE(VLOOKUP($C$6,CHOOOSE,3,FALSE),FUEL.A319,FUEL.320,FUEL.A321,FUEL.340.AB,FUEL.330),2)

$c$7 = fuel figure
$c$6 = aircraft registration



Below is aircraft registration

aircraft regstrationaircraft typeaircraft type
JYAYKA3211
JYAYJA3211
JYAYTA3211
JYAYVA3211
JYAIEA3302
JYAIFA3302
JYAIGA3302
JYAIAA3403
JYAIBA3403
JYAICA3403

<tbody>
</tbody>


below table for A340 aircrafts fuel.340.ab table


fuel den.773.779.785.797.803.809.815
weight
500

-1-2-2-2-2-2-2
1000-3-2-2-2-4-4-4
1500-2-3-3-3-3-3-3
20000000000
25001111111
30001111111
35002222111

<tbody>
</tbody>

below fuel index for A330 aircrafts. FUEL.A330

fuel den..775.780.785.790.795.80.805.815
weight33333333
2000
55555555
400088877766
600055544444
800011111111
1000000000000
12000-1-1-1-1-2-2-2-2

<tbody>
</tbody>


below fuel index for A321 aircfafts FUEL.A321 TABLE


fuel den..76.77.78.785.79.80.81.82
weight
1000-1-1-1-1-1-1-1-1
1500-1-1-1-1-1-1-1-1
2000-2-2-2-2-2-2-2-2
2500-4-4-4-4-4-4-4-4
3000-2-2-2-2-2-2-3-3
3500-100000-1-1
400011111111

<tbody>
</tbody>


PLZZZZZZZZZ HELP ME.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
fuel figure is same as fuel weight.fuel density values are the value below fuel density for example

fuel weight fuel density .760
1000 -1
1500 -2
2000 -2

-1,-2,-2 are the fuel index or fuel value
and so on. thank you
 
Last edited:
Upvote 0
If I understand correctly what you want, maybe this can helps:

Layout

aircraft regstrationaircraft typeaircraft type FUEL.A321fuel den.0,760,770,780,7850,790,80,810,82 aircraft regstrationweightfuel denResult
JYAYKA3211 weight JYAYK20000,779-2
JYAYJA3211 1000-1-1-1-1-1-1-1-1 JYAIE20000,7795
JYAYTA3211 1500-1-1-1-1-1-1-1-1 JYAIC20000,7790
JYAYVA3211 2000-2-2-2-2-2-2-2-2
JYAIEA3302 2500-4-4-4-4-4-4-4-4
JYAIFA3302 3000-2-2-2-2-2-2-3-3
JYAIGA3302 3500-100000-1-1
JYAIAA3403 400011111111
JYAIBA3403
JYAICA3403 FUEL.A330fuel den.0,7750,780,7850,790,7950,80,8050,815
weight33333333
200055555555
400088877766
600055544444
800011111111
1000000000000
12000-1-1-1-1-2-2-2-2
*
FUEL.340.ABfuel den0,7730,7790,7850,7970,8030,8090,815
weight
500-1-2-2-2-2-2-2
1000-3-2-2-2-4-4-4
1500-2-3-3-3-3-3-3
20000000000
25001111111
30001111111
35002222111
******************************************************************************************************************************************************
<colgroup><col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;" span="2"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;" span="8"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <tbody> </tbody>


Formula

Code: 
In S2

=VLOOKUP($Q2,
CHOOSE(VLOOKUP($P2,CHOOOSE,3,0),FUEL.A321,FUEL.A330,FUEL.340.AB),
MATCH($R2,INDEX(CHOOSE(VLOOKUP($P2,CHOOOSE,3,0),FUEL.A321,FUEL.A330,FUEL.340.AB),1,)))

Markmzz
 
Upvote 0
NADERASSALEH said:
fuel figure is same as fuel weight.fuel density values are the value below fuel density for example

fuel weight fuel density .760
1000 -1
1500 -2
2000 -2

-1,-2,-2 are the fuel index or fuel value
and so on. thank you
Click to expand...

Try to extend your aircraft registration table with the table names related to the aircrafts in the fourth column. Let's name the extended table RegTable.

And modify the tables like below, select the whole range including row and column headers, name it as: fuel.340.ab

weight/fuel den.0.7730.7790.7850.7970.8030.8090.815
500-1-2-2-2-2-2-2
1000-3-2-2-2-4-4-4
1500-2
-3-3-3-3-3-3
20000000000
25001111111
30001111111
35002222111

<colgroup><col style="mso-width-source:userset;mso-width-alt:4352;width:92pt" width="122"> <col style="width:48pt" span="7" width="64"> </colgroup><tbody>
</tbody>


Once you have that you can set up a smart formula:

C6 which houses an aircraft registration value like fuel.340.ab
C7 houses a fuel figure (weight)
C8 houses a density value
Rich (BB code): 
=VLOOKUP(
   C7,
   INDIRECT(VLOOKUP(C6,RegTable,4,0)),
   MATCH(C8,INDEX(INDIRECT(VLOOKUP(C6,RegTable,4,0)),1,0),1),1)

Hope this helps.
 
Upvote 0
dears,
thank you all i did try Mr. Aladin method and it worked as a charm. while testing i faced a small problem which is how to remember fuel density based on aircraft type i know that can be accomplished by data validation i did try but failed. can you plz help me.
 
Upvote 0
NADERASSALEH said:
dears,
thank you all i did try Mr. Aladin method and it worked as a charm. while testing i faced a small problem which is how to remember fuel density based on aircraft type i know that can be accomplished by data validation i did try but failed. can you plz help me.
Click to expand...

I think the following option would work:

Data validate C8 where we can pick out a density value, appropriate to the table we enter in C6...

Select C8
Activate Data | Data Validation
Choose the List option
Enter the following as source:

=INDEX(INDIRECT($C$6),1,0)

Click OK.

Hope the first item you see in the list is not very disturbing.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA Code Help
Replies
4
Views
267
Peter_SSs
Peter_SSs
J
  • Question
Combining VBA code together...need help!
Replies
3
Views
148
Fluff
Fluff
A
  • Question
Need a formula to calculate a MOD10 check digit for a string of 26 numbers.
Replies
2
Views
546
Georgiboy
Georgiboy
M
  • Solved
Need help modify to pick any first number of my choice
Replies
6
Views
251
motilulla
M
H
  • Solved
Index and match formula that ignores dynamic value
2
Replies
14
Views
373
FormR
FormR

Forum statistics

Threads
1,214,551
Messages
6,120,159
Members
448,948
Latest member
spamiki

Share this page

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