• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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..

Thanks in advance..
 

Attachments

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.
 

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,
 

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

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.

Please see the below file..
 

Attachments

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

Hui

Excel Ninja
Staff member
Out of interest I plotted the data in Columns K & L
upload_2014-9-4_16-40-55.png

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
 

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
upload_2015-1-18_13-30-19.png

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

Attachments

Top