Libreoffice Calc doing a linear regression with missing data values

Question

In Libreoffice calc, if you try to do a linear regression where some of the data is missing it just returns an Error Err:502. Is there a way for me to ask Calc to just drop/ignore the datapoints with missing values, without me having to explicitly delete the datapoints with incomplete values (since I want to keep this information)?

While doing the linear regression that doesn't seem to be an option. It would be easy to make an extra line with a boolean to say whether to use that datapoint in the linear regression or not, but then I don't know how to tell the linear regression to only use data based on the boolean. Is there for example a way to hide some of the columns from the linear regression based on a boolean?

In the above image, I would want to omit column D from the linear regression be I don't want to delete that column because I don't want to loose the data it contains.

Any help welcome.

Jim K · Accepted Answer · 2022-10-29 17:09:07Z

1

On another sheet, possibly hidden, you could use formulas to copy over the data to include. Then do the regression on that sheet and output the regression results back to the main sheet.

These formulas could no doubt be modified based on the boolean row if you want to do that rather than simply specifying directly in the formula which columns to use.

answered Oct 29, 2022 at 17:09

Jim K

14.1k2 gold badges26 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

patapouf_ai Over a year ago

Thanks!! Good idea to copy over to another sheet using formulas. Is then a way then to automatically delete columns based on a boolean?

patapouf_ai Over a year ago

Got it, the standard filter can filter based on columns, unfortunately not on rows, but it is possible to transpose the data by doing copy, then special paste transpose.

Jim K Over a year ago

Sounds like a good way to do it. I was thinking of using formulas that would look for the lowest column that contains TRUE, then use ADDRESS to grab those values. Using a filter seems simpler though.

Jim K Over a year ago

If the data were in columns to begin with (x1 in column A, x2 in column B), then there would be no need to transpose. It looks like that is what the Regression function defaults to.

Collectives™ on Stack Overflow

Libreoffice Calc doing a linear regression with missing data values

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related