Nested IF or VLookup, not sure how to procede

Conrodbob

New Member
Joined
Jul 25, 2014
Messages
1
Hi All,

I am using Microsoft Excel for Mac 2011 Version 14.4.3 (140616) Last update 14.4.3

I cannot seem to get my head around this problem. I have made an Excel spread sheet Table that is for what is known as a Helicopter Weight and Balance computation Table WB

The operator inputs weight values into one column - Column C.

In Column D there are set values representing a station position or Center of Gravity CG position (passenger seat, baggage compartment, etc)

In Column E there is the result of The C Cell x the D cell for each row known as the Moment value

The Column D value for one of the items - Fuel, is variable based on the weight value entered in the corresponding C cell. I have successfully utilized a Vlookup function to do this for the Table on the right side FUEL Station Moments Table

After all values have been entered a Total Weight is calculated in C19 from the Sum of Values of Column C and I have managed to insert an IF statement in C21 to tell me whether or not I have exceeded the Max allowable weight or not.

A Total 'Moment' Value E19 is calculated from the Sum of Values of Column E

A CG Position value is then calculated by dividing The sum from Column E by the Total weight Sum in Column C and placing this at the bottom of Column D in D20

My problem comes at the bottom of the sheet.

For any given Total weight at the bottom of Column C in C19 of the main Table I must be able to show and OK or Not OK answer in D21 to an If or Nested If statement or Some other statement as I am not sure what to write in.

I have another table The CG Reference Table on the bottom of the sheet. In column A is a list of weight values ranging from the lowest possible weight of the empty helicopter of 4,715.5 KG up to the Maximum allowable of 6,800 KG Spaced in 100KG increments from 4,800 upwards.

For each weight value there is a balance range it must fall into in order to be safe to fly. The corresponding minimum figures are listed in column B and the corresponding maximum figures in column C

So, what I need is to be able to Look at the Value of the Main table Column C - C19 Total Weight (lets say it is 5,525.5) then the program must reference the lower table The CG Reference Table and be able to tell me if the CG Value that is in Table WB column D - D20 falls within the allowable range corresponding to that weight value (or closest) in the left side of CG Ref Table column A and if so give me an OK (true), Not OK (false). Or Green Check Mark instead of OK

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: "Arial Narrow",sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { border: 0.5pt solid windowtext; }.xl64 { font-weight: 700; border: 0.5pt solid windowtext; }.xl65 { text-align: center; border: 0.5pt solid windowtext; }.xl66 { text-align: center; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(166, 166, 166); }.xl67 { text-align: center; border: 0.5pt solid windowtext; }.xl68 { border: 0.5pt solid windowtext; }.xl69 { font-weight: 700; text-align: center; border: 0.5pt solid windowtext; }.xl70 { text-align: center; }.xl71 { }.xl72 { }.xl73 { text-align: center; border: 0.5pt solid windowtext; }.xl74 { text-align: center; border: 0.5pt solid windowtext; }.xl75 { font-weight: 700; text-align: center; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% yellow; }.xl76 { font-weight: 700; text-align: center; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(141, 180, 226); }.xl77 { font-weight: 700; text-align: center; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(141, 180, 226); }.xl78 { font-weight: 700; text-align: center; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(141, 180, 226); }.xl79 { text-align: center; border: 0.5pt solid windowtext; }.xl80 { text-align: center; border: 0.5pt solid windowtext; }.xl81 { font-family: "Zapf Dingbats",sans-serif; text-align: center; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% lime; }.xl82 { font-family: Calibri; text-align: center; border: 0.5pt solid windowtext; }.xl83 { border: 0.5pt solid windowtext; }.xl84 { text-align: left; vertical-align: middle; padding-left: 12px; }</style>
Aircraft: AW139BMHMFUEL STATIONS MOMENTS
256208155.2
ItemWeight KgST:MMLong MomentBL:MMLat Moment506209310.4
Empty Weight4715.55287.06249334945.0923982.5756210465.8
P1602820169200550330001006210621.0
P2802820225600-550-440001256210776.2
SeatM/F/C/101506210931.4
A34150737017562101086.7
B34150254020062101241.9
C34150-254022562101397.1
D34150-737025062101552.4
E.55660640027562101707.6
F.55660-640030062101862.9
Cabinet contents254591091832562102018.1
Fuel65062144039100.035062112173.9
Oil16687511000037562112329.1
Loose items040062112484.4
Baggage277001540042562112639.7
MGW5525.505339.5629503712.002.3512982.5045062112795.0
ST:M5.33960.002347562112950.2
OK/Not OKOK50062123106.0
52562123261.3
55062123416.6
57562123571.9
60062133727.8
Weight (Kg)Station Min (M)Station Max (M)62562133883.1
68005.23805.480TRUE65062144039.1
67005.22645.486TRUE67562144194.5
66005.21485.49270062144349.8
65005.20325.49872562154505.9
64005.19165.50475062154661.3
63005.18005.51077562164817.4
62005.16845.51680062174973.6
61005.15685.52282562195130.7
60005.14525.52885062215287.9
59005.13365.53487562235445.1
58005.12205.54090062255602.5
57005.11045.54692562255758.1
56005.09885.55295062265914.7
55005.08725.55897562276071.7
54005.07565.564100062286227.9
53005.06405.570102562286384.1
52005.05245.576105062296540.4
51705.00005.582107562296696.6
51005.00005.588110062306852.9
50005.00005.594112562307009.3
49005.00005.600115062317165.6
48505.00005.595117562317322.0
48005.00005.588120062327478.4
47265.00005.570122562327634.8
125062337791.3
127062337915.9

<tbody>
</tbody>
(Grid coordinates didn't print)


I do so hope I have been clear on what I am trying to accomplish. And I thank anyone who is able to help me solve what function should go in D21 Longitudinal balance. Whatever it is I will need to do similar for F21 for the Lateral balance for Columns F and G


Again, Thanks!

Bob
 

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.

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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