'find a formula/constant to replace a table into just one formula
First I need to apologize as maybe the below question is maybe just stupid, so excuse my little to zero knowledge in mathematics.
I've been working a long time with VBA and got a little bit familiar with Python, I hope someone can help me out with the below question.
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 and here is where the issue occurs. Adding this all into a VBA module will first of all makes it's terrible slow to open or to save. Of course i can save the full table to a worksheet and cope as a lookup table. But thinking this over it would be much better/cleaner to have it just in a formula which can just generate the full table.
As said I'm not a data analyst and a real noob when it comes to mathematics. I just hoped there was just 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. Sure this can could be described better but sorry I do not know all the mathematical terms for this.
Maybe the whole is just not possible but I'm open for any feedback.
Extra addition to clarify:
I can understand it is not very clear, and I sincerely apologize! My question is if there is some kind of 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
Solution 1:[1]
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.
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.
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.
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.
And let's rename the table of unpivoted data, just for convenience. I named this table Data
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.
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:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Vitalizzare |