Re: [libreoffice-users] Calc 24.8.3.2 HOWTO Manually calculate Linear Trend Line

Hi Hylton,

You can use the functions LINEST for getting m and b combined, or SLOPE 
for m and INTERCEPT for b, in principle.

If you want a forecast for e.g. Jan 2025 from the Jan values of 2016 to 
2024 then functions FORECAST and TREND are candidates.

But the arrangement of your data is not suitable, see below.

Hylton Conacher (ZR1HPC) schrieb am 12.01.2025 um 14:59:
> Hi All,
>
> I have a 32 column spreadsheet which I need to be able to 'manually' 
> calculate the last known Trendline y value.
>
> I am aware of inserting Trendlines via the Calc app, but finding the 
> FINAL y value requires multiple graph scale changes to determine the Y 
> intercept, which is not conducive to time saving.
>
> I have also done multiple searches on my friend Google and despite 
> finding out that the equation for a Trendline is y=mx+b, from where to 
> get the figures seems to be a well kept secret.
>
> Below is a subset of my data and the Linear Trendline I would like to 
> calculate, and have change when I expand it across the other data 
> columns. The Trendline required is for data in column C2,f2,i2 ie 
> Month-end Rainfall.
>
> COLUMNS:
> A1: Month(Jan->Mar)
> B1: 2016 Monthly Rainfall to last Rain date
> C1: 2016 Month-end Rainfall
> D1: 2016 Annual Running Total
> E1: 2017 Monthly Rainfall to last Rain date
> F1: 2017 Month-end Rainfall
> G1: 2017 Annual Running Total
> H1: 2018 Monthly Rainfall to last Rain date
> I1: 2018 Month-end Rainfall

What is the difference between "Monthly Rainfall to last Rain date" and 
"Month-end Rainfall"?
> A1    B1    C1    D1    E1    F1    G1    H1    I1
> Jan    1.0    1.0    1.0    0.0    30.0    30.0    4.5    4.5
> Feb    0.0    0.0    1.0    0.0    1.0    31.0    0.0    14.0
> Mar    0.0    53.0    54.0    0.0    7.0    38.0    0.0    20.3

It would be better to have an arrangement, where column B contains the 
values from which you want to get the linear trend and column A contains 
for each value the date as number. You can create such tables from your 
data be using the FILTER function or better use a more suitable 
arrangement from the beginning.

A table of January totals, for example:

       A        B      C
-----+---------+------------+-----
1    |Jan Year |Month Total |
2    |2016     |1.0         |
3    |2017     |30.0        |
4    |2018     |4.5         |
..   |...      |...         |
10   |2024     |...         |

Then you calculate the parameters m and b of a linear regression y=mx+b by
(A) {=LINEST(B2:B10;A2:A10;1;0)}
    LINEST is an array function. Finish writing it with Ctrl+Shift+Enter.
    The first returned value is m, the second one is b.
(B) =SLOPE(B$2:B$10;A$2:A$10)
    That returns the parameter m
(C) =INTERCEPT(B2:B10;A2:A10)
    That returns the parameter b

To calculate a forecast on the linear regression line for year 2025 
(that is m*2025+b)
(D) =FORECAST(2025;B$2:B$10;A$2:A$10)
(E) {=TREND(B$2:B$10;A$2:A$10;2025;1)}
    TREND is an array function. Finish writing it with 
Ctrl+Shift+Enter. TREND allows to forecast not only one value but an 
array of values. If the year numbers 2025, 2026, 2027 are in cell range 
F20:F22 for example, you can use {=TREND(B$2:B$10;A$2:A$10;F$20:F$22;1)}

I hope this gives you same ideas about calculating linear regression.
You can find more info about the mentioned functions in the Help or in 
the Wiki
https://wiki.documentfoundation.org/Documentation/Calc_Functions/List_of_Functions

Kind regards,
Regina


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy