• 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

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

  • TRIM.xlsx
    9.7 KB · Views: 5
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.
 
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..
 
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,
 
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

  • TRIM LM.xlsx
    11.2 KB · Views: 2
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
 
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))
 
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

  • TRIM LM.xlsx
    12.2 KB · Views: 3
Last edited:
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

  • TRIM LM2.xlsx
    12.5 KB · Views: 7
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
 
This is the working formulas. You can see at the attachment.

Thanks for your helps. Appreciated.
 

Attachments

  • index.xlsx
    13 KB · Views: 4
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

  • index.xlsx
    16.2 KB · Views: 5
You must log in or register to reply here.
Back
Top