Excel using a graph trend-line formula to calculate y values from x values

Question

I have a data set which needs to be offset by another data set.

My initial set is a sample being compressed, with the data being X = load (N), Y = Compression displacement.

My offset is the shift in the testing setup which is again measured in X = load (N), Y = Compression displacement.

I managed to get a trend line which sort of fits the offset data, which looks like the below image

It's been a while since I did A level maths and I am drawing a blank on how to use this formula with the 'E' notation to get the full range of Y values from each X value at 0.5N intervals.

If we take the first section for example: 2.76452571251802E-16x^5

would I type in 0.0000276452571251802x (where x is the cell with the required x value)?

Here is the full formula typed out which may be helpful;

(edit) - this formula seems to work: = (2.76452571251802E-16*(I42^5))-(1.32927081318371E-12*(I42^4))+(2.37493205750957E-09*(I42^3))-(1.91284793017854E-06*(I42^2))+(0.0012583312744059*I42)+(0.0276409810651471)

Is there an easy way to get excel to give me a y value for a given x value which doesn't require me to manually type out the formula it is providing?

Any help of information would be greatly appreciated.

Answer 1

Not quite. The E-16 means move the decimal point 16 places to the left. Then you multiply that number by (x^5).

So...

However, to calculate Y from X, just separate the function into terms of the form a*x^b where a is the coefficient and b is the exponent of each term, then it becomes quite easy with SUMPRODUCT

Here's the sumproduct version from cell H3 above:

=SUMPRODUCT($C$3:$C$8,F3^$D$3:$D$8)

And the uglier long form from cell J3:

=$C$3*F3^$D$3+$C$4*F3^$D$4+$C$5*F3^$D$5+$C$6*F3^$D$6+$C$7*F3^$D$7+$C$8