How to calculate the best fit value with script?

[#1]

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.

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

Thank you experts!

[#2]

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!

[#3]

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。应该就可以了。

[#4]

非常感谢！

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

我还是没有想到好方法。

再次感谢!

[#5 · Accepted solution]

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