Solved: Regression Calculations (Equivalent of LOGEST from... - Page 2

8675309 · 12-19-2023 07:13 AM

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

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

View solution in original post

Regression Calculations (Equivalent of LOGEST from excel)