Interpolation/aproximation of unknow values by trend line

Hello guys,

I've got a problem which i am not sure how to handle.

I have few values from very low quality chart, and i want excel to fill all other gaps - it might not be as accurate as the real one. The easiest way is to do that by trend line, but as far as I know excel only displays the formula for the trend line and i cannot "acquire" it in any cell. I know i can do that by hand but i want to have this process automated to do something like shown in attachment.

I have in attachment one sheet working as catalog of pumps (inlcuding inside one picutre of pump, one picture of chart, model, company and values which are read from chart (y) scalled to the range of x 0 to 100 (step by 2)) In the second sheet (row 2, column B) we have list to choose which pump we want from our catalog - along with changing these two pictures I want excel to get these written down by hand values copied to row 24 columns C:BA.

And after all that preparations, how can i make excel to fill the gaps in my values? Or give me trend line formula? Would be awesome if that could be done without VBA because i certainly do not know how to use. I've tried a lot with REGLIW, and other like that but i do not quiet understand how it works.

The main goal of it is to after some diffrent calculations I could go and check exact value of that pump in desired spot.

Or maybe u have diffrent idea how can i make it work.

I hope guys you will help me with that
ASDG

You can use LINEST to get the equation of the line, the format needed to get a 3rd order polynomial is:

=LINEST(y_values,x_values^{1;2;3})

This will return an array of 4 cells. If the formula of the line is:

y = ax^3 + bx^2 + cx + d

Then the 4 cells are in the order a,b,c,d.

For your inputs you need to filter out the blank cells. In 365 you can just use FILTER:

=LINEST(FILTER(C24:BA24,C24:BA24<>""),FILTER(C22:BA22,C24:BA24<>"")^{1;2;3})

I see you have 2019, so another method would be needed. Something like this might work (it's difficult for me to check, see attached file):

=INDEX(LINEST(INDEX($C$24:$BA$24,AGGREGATE(15,7,IF($C$24:$BA$24<>"",COLUMN($C$24:$BA$24)-COLUMN($C$24)+1,""),COLUMN(A1:INDEX(1:1,COUNTA($C$24:$BA$24))))),INDEX($C$22:$BA$22,AGGREGATE(15,7,IF($C$24:$BA$24<>"",COLUMN($C$24:$BA$24)-COLUMN($C$24)+1,""),COLUMN(A1:INDEX(1:1,COUNTA($C$24:$BA$24)))))^{1;2;3}),1)

Change the 1 and the end (in red) to 2, 3, 4 to get the other parameters.

I can see that my excel is struggling with that part of your formula

A1:INDEX(1:1,COUNTA($C$24:$BA$24))

Also did u attach any file because i do not see anything.

Oops, I thought I had attached a file but trying to do it again it seems it was too large. I've deleted the pictures and the other sheet as they are redundant.

Does it work now?

Works brilliant!

Now i have in this 4 cells, coefficients a,b,c,d and they will adapt every time i change my known values? That's great! Also one more question not related to the first question but to the file u've seen. How can i manage to get diffrent sets of this know values typed by hand in catalog sheet dependent on choosen model in row 2 column B?

You could use something like this in C24:

=INDEX('Katalog - pompy'!$E$1:$BC$2,MATCH('Dane - pompa'!$B$2,'Katalog - pompy'!$C$1:$C$2,0),COLUMN()-COLUMN($C$24)+1)

The problem is with blank cells; it's difficult to get these to remain blank. If the data will always be in the same columns then the simplest solution would be to just use the formula for the columns with data.

That's the main problem, data will be different to each pump. But i will try to work something out with that what u gave me!

Thank you very much