# Need a formula..

#### akinkaraman

##### Member
I need a formula for 7th row which will get the trim value of 6th row from the chart.

I want to get the results like the 10th row such as for 24.4, 25.8 etc..

#### Attachments

• 9.7 KB Views: 5

#### Somendra Misra

##### Excel Ninja
Hi,

Your data result expected and description are totally not understood, can you explain it more clearly.

Regards,

#### akinkaraman

##### Member
Such as at A Zone the CG is 22,5 and 22 is 2 Nose UP and 23 is 1,625 Nose UP at the chart which means CG of 22,5 should be 1,8125 Nose UP and I need a formula to write it at B7 and at H Zone the CG is 32,1 and 32 is 0,875 Nose DOWN and 33 is 1,00 Nose DOWN at the chart which means 32,1 should be 0,8875 Nose DOWN and I need the formula for H7..

So it goes like that..

The formula should calculate the value due to decimals via the chart.

#### Somendra Misra

##### Excel Ninja
Are the values 1.8125 & 0.8875 are average of numbers?

Regards,

#### akinkaraman

##### Member
For 22,5

22 is 2 Nose UP and
23 is 1,625 Nose UP

A= (2-1,625)/10
Because of 22,5 => B = A*5
C= 2-B

=> 2-(((2-1,625)/10)*5)

My english is not good, sorry about that..

#### Somendra Misra

##### Excel Ninja
Try below formula in B7 and copy right.

=INDEX(\$L\$5:\$L\$23,MATCH(B6,\$K\$5:\$K\$23))-(((INDEX(\$L\$5:\$L\$23,MATCH(B6,\$K\$5:\$K\$23))-INDEX(\$L\$5:\$L\$23,MATCH(B6,\$K\$5:\$K\$23)+1))/10)*(MOD(B6,1)*10))&" "&INDEX(\$M\$5:\$M\$23,MATCH(B6,\$K\$5:\$K\$23))

Regards,

#### akinkaraman

##### Member
It says the formula has an error..

Can you send me the file, please?

See the file.

Regards,

#### Attachments

• 10.8 KB Views: 6

#### akinkaraman

##### Member
It is working good.. Thank you.

#### Somendra Misra

##### Excel Ninja
Thanks for the feedback, and welcome back!!!

Regards,

#### akinkaraman

##### Member
Hello Somendra Misra, this formula is giving wrong results for 28.8, 28.9, 29.1, 29.2, 29.3, 29.4, 29.5, 29.6, 29.7, 29.8, 29.9 and 36

Last edited:

#### Luke M

##### Excel Ninja
To get the decimals to display better, I would suggest changing formula in B7 to:
=TEXT(INDEX(\$L\$5:\$L\$23,MATCH(B6,\$K\$5:\$K\$23))-(((INDEX(\$L\$5:\$L\$23,MATCH(B6,\$K\$5:\$K\$23))-INDEX(\$L\$5:\$L\$23,MATCH(B6,\$K\$5:\$K\$23)+1))/10)*(MOD(B6,1)*10)),"0.000")&" "&INDEX(\$M\$5:\$M\$23,MATCH(B6,\$K\$5:\$K\$23))

Also, due to how formula does lookups, it would help if M16 = "Nose DOWN"

#### Attachments

• 11.2 KB Views: 2

#### akinkaraman

##### Member
29 is 0 and it is not Nose UP or Nose DOWN

The Plane can be Nose UP if the CG is less than 29 and be Nose DOWN if the CG is more than 29 and at 29 it is neither Nose UP nor Nose DOWN

1.200 and 0.000 should be shown as 1.20 and 0

If the result is 1.125 that is fine it can be 3 digit after ''.'' but if it is 1 it will be shown 1.00 and if it is 1.250 it will be 1.25

And 22.5 CG must be 1.8125 not 1.813

Thanks..

Akın

#### Hui

##### Excel Ninja
Staff member
Change Cell M16 to a Blank value

Select L5:L30
Ctrl 1
Number, Custom
use a Custom Format of 0.00;-0.00;0

in Cell B6:
=TEXT(INDEX(\$L\$5:\$L\$30,MATCH(B6,\$K\$5:\$K\$30))-(((INDEX(\$L\$5:\$L\$30,MATCH(B6,\$K\$5:\$K\$30))-INDEX(\$L\$5:\$L\$30,MATCH(B6,\$K\$5:\$K\$30)+1))/10)*(MOD(B6,1)*10)),"0.00;-0.00;0")&" "&INDEX(\$M\$5:\$M\$30,MATCH(B6,\$K\$5:\$K\$30))

#### akinkaraman

##### Member
29.1, 29.2, 29.3, ... , 29.9 doesn't show Nose DOWN

and it shows 2 digits after ''.''

But it is sometimes 2 and sometimes 3.

#### Attachments

• 12.2 KB Views: 3
Last edited:

#### Luke M

##### Excel Ninja
We can fix the decimal situation I think by changing format to "0.00#", so we don't show a trailing 0 in thousandths place. Then, to get 29.1, 29.2, etc. to show up, we'll add a fractionally small increment above 0 (see attached).

#### Attachments

• 12.5 KB Views: 7

#### Hui

##### Excel Ninja
Staff member
Out of interest I plotted the data in Columns K & L

Is the curve meant to have the bumps in it as highlighted above?

If for example a value of 24.7 was required, using your formula you get a nice value of 1.2
Using a straight linear interpolation between the 24 and 25 values you get a nice 1.2

This would result in a much simpler solution if allowable

#### akinkaraman

##### Member
This is the working formulas. You can see at the attachment.

#### Attachments

• 13 KB Views: 4

#### Hui

##### Excel Ninja
Staff member
I'm note sure if this is a question?

I added a dummy set of data where I manually modeled the Curve
using the 2 formula:
Y=(-3/11)*X + 29*3/11 [x<=29]
Y=(1.5/6)*X - 7.3 [x>30)

It is shown as the red line below

Note that it doesn't have the bumps built into your original data
See attached file:

#### Attachments

• 16.2 KB Views: 5

#### akinkaraman

##### Member
Trim values at the chart are totally anomalous however the formula at B4 is simply generating what I needed.

Staff member
Ok

#### akinkaraman

##### Member
Thank you Mr. Hui. I am learning very much with helps of you. Great forum.. and wonderful people are here.