How do I enter a huge HUGE =correl(... array?

[MikeColley]

I can’t type in what I want, it is just too too TOO big of a job. I want to do a very large data set correlation with another data set of identical size. Each data set is 500 days vertical in LibreOffice CALC. One data set horizontally is columns of TemperatureOfCity#1 through TemperatureOfCity#600 and the vertical axis is 500 days for each column. The other data set horizontally is columns of HumidityOfCity#1 through HumidityOfCity#600 and the vertical axis is 500 days for each column.

Notice one data set is Temperature and the other data set is Humidity. Each data set contains 500(days)X600(cities) pieces of data.

I would like to get 360,000 correlations. That would be to correlate today’s temperature in all the cities with tomorrow’s humidity in all the cities. That means an array of correlations of 600(TemperatureCityColumns) x 600(HumidityCityColumns) or 360,000 entries. I might just burn up the i7 CPU trying to figure this out wink wink. The problem is that I can’t type in 360,000 correlation entries, I just don’t have enough time left.

Example

=CORREL(AA1001:AA1501,AA2001:AA2501) correlates TemperatureOfCity#1 to HumidityOfCity#1 over 500 days

=CORREL(AB1001:AB1501,AA2001:AA2501) correlates TemperatureOfCity#2 to HumidityOfCity#1 over 500 days

The problem is I don’t have enough time left to enter 360,000 =correl(… formulas.

Is there some way to automate this? Or has someone already done it so I can use their work?

I can write a [BASIC] program to create an ASCII file with all the formulas in it but I don’t know how to get those ASCII characters into LO Calc as formulas. -OR- Maybe there is an easy way I don’t know about. I expect to get 360,000 results if I don’t burn up the CPU.

This is an LO CALC BIG array question, I’m trying to do a correlation and can’t figure a realistic way to do it. Any suggestions?

Thanks for your time - Mike

[JohnSUN]

It’s not difficult, Mike, it won’t take long.
Use this trick - use the OFFSET(), COLUMN() and ROW() functions to form all formulas of this matrix in one go.

Suppose your data is on a sheet called Sheet1.

Create a new sheet, in the range address field, enter B2:WC601 and press Enter.

Calc will select a matrix of 600x600 cells. Now press F2 to go to the mode of editing cells and enter the formula

=CORREL(OFFSET($Sheet1.$Y$1001:$Y$1501;0;COLUMN());OFFSET($Sheet1.$Y$2001:$Y$2501;0;ROW()))

Cross your fingers and press Alt + Enter. Done, you have 360,000 formulas. If the processor does not burn, then after some (rather long) time you will see the result. Otherwise, you will know that it burned out for a great goal (I hope not).

The first row and column A are left blank so that you can add TemperatureOfCity#XXX and HumidityOfCity#XXX (You can fill in a row by simply copying the header cells from the source table, and fill the column with =TRANSPOSE($Sheet1.AA1000:XB1000) )

Good luck!

[erAck]

Neat and nifty

[dscheikey]

Hi, Mike,

I have a little more time than you do, so I quickly wrote you the formulas. No joke!
I used the indirect and address function. It’s pretty simple. I’ve build a sample document. By the way, with my i5-8265U processor it took me 35 seconds to calculate it.
For Upload here in Ask LO maximum file size is 1048576K
So I Upload the File in Firefox Send. Available only in the next 7 days.
Link to FF Send

If this has answered your question, please mark the question as answered. The little hook next to the answer.

Jürgen