Regression Calculations (Equivalent of LOGEST from excel)

[8675309]

Does anyone know of any way to do an exponential regression fit in AppSheet?  I'm very new to the app (and no-code programming in general) and I'm trying to find a way to automatically fit an exponential trend line through some data.  Example...

Y data points:  100 80 83 75 65 61 55 57 51 49 50 48

X data points: 1 2 3 4...

I can get this to work in excel using LOGEST, but haven't really figured out how to do this in AS.  I want to find an equation that will fit to the data points that I can then use to forecast or project based on X being 50 or 100.

Any help is greatly appreciated

[Denny774]

Hey man,

it's pretty simple by mimiking the LOGEST function in appsheet as a workaround so to speak.

The equation for the curve is:

y = b*m^x

or

y = (b*(m1^x1)*(m2^x2)*_)

if there are multiple x-values, where the dependent y-value is a function of the independent x-values. The m-values are bases corresponding to each exponent x-value, and b is a constant value. Note that y, x, and m can be vectors. The array that LOGEST returns is {mn,mn-1,...,m1,b}.

Syntax

LOGEST(known_y's, [known_x's], [const], [stats])

The LOGEST function syntax has the following arguments:

• known_y's    Required. The set of y-values you already know in the relationship y = b*m^x.

  • If the array known_y's is in a single column, then each column of known_x's is interpreted as a separate variable.

  • If the array known_y's is in a single row, then each row of known_x's is interpreted as a separate variable.

• known_x's    Optional. An optional set of x-values that you may already know in the relationship y = b*m^x.

  • The array known_x's can include one or more sets of variables. If only one variable is used, known_y's and known_x's can be ranges of any shape, as long as they have equal dimensions. If more than one variable is used, known_y's must be a range of cells with a height of one row or a width of one column (which is also known as a vector).

  • If known_x's is omitted, it is assumed to be the array {1,2,3,...} that is the same size as known_y's.

• const    Optional. A logical value specifying whether to force the constant b to equal 1.

  • If const is TRUE or omitted, b is calculated normally.

  • If const is FALSE, b is set equal to 1, and the m-values are fitted to y = m^x.

• stats    Optional. A logical value specifying whether to return additional regression statistics.

  • If stats is TRUE, LOGEST returns the additional regression statistics, so the returned array is {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r 2,sey; F,df;ssreg,ssresid}.

  • If stats is FALSE or omitted, LOGEST returns only the m-coefficients and the constant b.
    
    
    
    

    Cheers

[8675309]

Thanks for the Reply.  

So, perhaps I'm not fully understanding the reply, but the provided equation still appears to leave 2 unknown variables (b and m).   The reason I'm using the LOGEST regression function is to take ONLY the x/y's and to solve for b and m.  In other words, I want the app to take x/y's (and only the x/y's) from a user and spit out the b and m.  So is there a regression or iterative function or tool that is available in AS?  

[Denny774 · Accepted solution]

Here you go man,

this is a mimicked LOGEST fx in AppSheet 

//example array values
X1 = {1 , 2 , 3 , 4 , 5}
Y1 = {100 , 83 , 80 , 75 , 70}

AppSheet fx / expression

LIST(
  (SUM(LIST(((INDEX([X1],1) - AVERAGE([X1]))*(INDEX([Y1],1) - AVERAGE([Y1]))), ((INDEX([X1],2) - AVERAGE([X1]))*(INDEX([Y1],2) - AVERAGE([Y1]))), ((INDEX([X1],3) - AVERAGE([X1]))*(INDEX([Y1],3) - AVERAGE([Y1]))), ((INDEX([X1],4) - AVERAGE([X1]))*(INDEX([Y1],4) - AVERAGE([Y1]))), ((INDEX([X1],5) - AVERAGE([X1]))*(INDEX([Y1],5) - AVERAGE([Y1]))))) / SUM({POWER(INDEX([X1],1) - AVERAGE([X1]), 2), POWER(INDEX([X1],2) - AVERAGE([X1]), 2), POWER(INDEX([X1],3) - AVERAGE([X1]), 2), POWER(INDEX([X1],4) - AVERAGE([X1]), 2), POWER(INDEX([X1],5) - AVERAGE([X1]), 2)})), 
  (AVERAGE([Y1]) - (SUM(LIST(((INDEX([X1],1) - AVERAGE([X1]))*(INDEX([Y1],1) - AVERAGE([Y1]))), ((INDEX([X1],2) - AVERAGE([X1]))*(INDEX([Y1],2) - AVERAGE([Y1]))), ((INDEX([X1],3) - AVERAGE([X1]))*(INDEX([Y1],3) - AVERAGE([Y1]))), ((INDEX([X1],4) - AVERAGE([X1]))*(INDEX([Y1],4) - AVERAGE([Y1]))), ((INDEX([X1],5) - AVERAGE([X1]))*(INDEX([Y1],5) - AVERAGE([Y1]))))) / SUM({POWER(INDEX([X1],1) - AVERAGE([X1]), 2), POWER(INDEX([X1],2) - AVERAGE([X1]), 2), POWER(INDEX([X1],3) - AVERAGE([X1]), 2), POWER(INDEX([X1],4) - AVERAGE([X1]), 2), POWER(INDEX([X1],5) - AVERAGE([X1]), 2)})) * AVERAGE([X1]))
)

OUTPUT
{-6.8 , 102.0}

===========================

Alternatively you can use AI Predictive Models in AppSheet to achieve that.

===========================

Cheers