cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
The Discovery Summit 2025 Call for Content is open! Submit an abstract today to present at our premier analytics conference.
Choose Language Hide Translation Bar
View Original Published Thread

How to calculate the best fit value with script?

lala
Level VIII

Hello everyone!

As shown in the figure: There are several sets of data, and their data are relatively close.

Each set of data has two values, large and small.

How to use scripts to find the optimal value of the red horizontal line in the figure:

It is required that this horizontal line is close to one of the endpoints that can hold the most data, and this line is required to be horizontal.

undefined

 

I tried using least squares for regression, but still not ideal.

 

Thank you experts!

This post originally written in Chinese (Simplified) and has been translated for your convenience. When you reply, it will also be translated back to Chinese (Simplified).

1 ACCEPTED SOLUTION

Accepted Solutions
lwx228
Level VIII

回复: 如何用脚本计算出最符合的值?

x = Round( Beta[1] + 5 * Beta[2], 0 );

View solution in original post

4 REPLIES 4
lala
Level VIII


回复: 如何用脚本计算出最符合的值?

dt = Current Data Table();
m = Mean( dt[0, 2 :: 3] );
Column( "S" ) << Formula( If( Abs( H - m ) > Abs( L - m ), L, H ) );
dt << run formulas;
Column( "S" ) << deleteFormula;
ar = dt << GetAsMatrix( {4, 1} );
{Beta, VarBeta} = Least Squares Solve( ar[0, 1], ar[0, 2] );

x = round(Beta[1],0);

Thanks Experts!

frank_wang
Level IV

回复: 如何用脚本计算出最符合的值?

Hi Lala

我的理解是你需要一个值,这个值得取值范围为所有数据的最小值到最大值这个范围。这个值需要和11个组的两个端点进行条件判断。

我的思路是既然是一根水平线,因此f(x)=b。换个方法就是取一个值b,让b距离11个组最近的那个点的值的求和最小。

即,假设结果为b。那么整体的函数就是求, Y=min(abs(y1max-b),(y1min-b))+min(abs(y2max-b),(y2min-b))+...min(abs(y11max-b),(y11min-b))。对Y求最小。

你可以尝试整理这个函数然后求Y最小值时b的值,然后带入函数f(x)=b。应该就可以了。

心若止水
lala
Level VIII

回复: 如何用脚本计算出最符合的值?

非常感谢!

假如这样的数据超过一百组、也要这样编辑公式吗?

我还是没有想到好方法。

 

再次感谢!

lwx228
Level VIII

回复: 如何用脚本计算出最符合的值?

x = Round( Beta[1] + 5 * Beta[2], 0 );