2

In the below table I got some values where the column headers represent the density where the first column of the table represent a temperature.

Density/Temp 0.5 0.505 0.51 0.515 0.52 0.525 0.53 0.535 0.54 0.545 0.55
-2.5 1.051 1.05 1.048 1.047 1.046 1.045 1.043 1.042 1.041 1.04 1.039
-2 1.05 1.049 1.047 1.046 1.045 1.044 1.042 1.041 1.04 1.039 1.038
-1.5 1.048 1.047 1.046 1.044 1.043 1.042 1.041 1.04 1.038 1.038 1.037
-1 1.047 1.046 1.044 1.043 1.042 1.41 1.039 1.038 1.037 1.037 1.036
-0.5 1.046 1.045 1.043 1.042 1.041 1.04 1.038 1.037 1.036 1.036 1.035
0 1.044 1.43 1.042 1.041 1.04 1.038 1.037 1.036 1.035 1.035 1.034
0.5 1.043 1.042 1.04 1.039 1.038 1.037 1.036 1.035 1.034 1.033 1.032
1 1.41 1.04 1.039 1.038 1.037 1.036 1.035 1.034 1.033 1.032 1.031
1.5 1.04 1.039 1.037 1.037 1.036 1.035 1.034 1.033 1.032 1.031 1.03
2 1.038 1.038 1.036 1.035 1.034 1.033 1.032 1.031 1.03 1.03 1.029
2.5 1.037 1.036 1.035 1.034 1.033 1.032 1.031 1.03 1.029 1.029 1.028
3 1.036 1.035 1.033 1.033 1.032 1.031 1.03 1.029 1.028 1.028 1.027
3.5 1.034 1.033 1.032 1.031 1.03 1.03 1.029 1.028 1.027 1.027 1.026
4 1.033 1.032 1.031 1.03 1.029 1.029 1.028 1.027 1.026 1.026 1.025
4.5 1.031 1.03 1.029 1.029 1.028 1.027 1.026 1.026 1.025 1.025 1.024
5 1.03 1.029 1.028 1.027 1.027 1.026 1.025 1.025 1.024 1.023 1.023
5.5 1.028 1.028 1.027 1.026 1.025 1.025 1.024 1.023 1.022 1.022 1.022

Now my actual table is a lot bigger. Adding this all into a VBA module will make it terribly slow to open or to save.
Of course I can save the full table to a worksheet and cope as a lookup table. But it would be much better/cleaner to have it in a formula which can just generate the full table.

Is there some software available or some Python module where I could open the table as a csv file which would be analyzed and get some kind of formula constant.

I can understand it is not very clear. Is there some way to retrieve the factor from the table which is defined by the X and Y for a temperature and a density? X and Y would give you a coordinate which represents a value in this table.
Now the question is with the temperature and density if there would be a factor/formula to replace the table so I can calculate the value which is the coordinate for X and Y?

Community
  • 1
  • 1
Joas van Doeselaar
  • 21
  • 3

1 Answers1

3

I assume that you want to find an approximation function, otherwise there's nothing to talk about. From what I can see, your data can be approximated by a linear formula.
enter image description here

We can use a linear regression or, in this case, the method of ordinary least squares. Our final formula will look like f(Density, Temperature) = k1*Density + k2*Temperature + k3. But first, let's fix provided data.

enter image description here

Next, I suggest to normalize data representation. Put the Temperature parameter in a separate column. Use Power Query Editor to load your data and apply unpivoting temperature columns.

enter image description here

Then we have to add an additional column to the normalized data for a free coefficient k3 in the approximation formula. All its values should be equal 1.

enter image description here

And let's rename the table of unpivoted data, just for convenience. I named this table Data

enter image description here

After all preparation, put this formula in any cell on a new sheet:

=LET(MatrixA; Data[[Density]:[Coeff]]; MatrixB; Data[Value]; MMULT(MMULT(MINVERSE(MMULT(TRANSPOSE(MatrixA);MatrixA));TRANSPOSE(MatrixA));MatrixB))

In the result you'll get coefficients k1, k2, k3 for our desired formula.

enter image description here

I've got this result with provided data :

f(density, temperature) = -0.0025*density - 0.1783*temperature + 1.1329

The expected absolute value of error is 0.003 in this case.

For more details I recommend to watch this video:

Vitalizzare
  • 4,496
  • 7
  • 13
  • 32