Store Excel value
 

Hello everyone,

I am new to this forum and new to Excel. I am a polymer chemist and have employed SPC for many years but am now trying to refresh my knowledge on statistics and use Excel for the calculations and ploting etc.

I have made a linear regression graph that works fine and calculates a Y-value at a given X-value (i.e. Y =.......when X is 225 Hz). Let us say this value is in cell B20

When I do a new "test" the regression line changes (obviously) and so does the value in cell B20 (obviously).......so far all is well.

I have also created a control chart with an avearage line and upper control limit and lower control limit. When I take the different values created in cell B20 from the different "tests" they plot nicely.....so far all is well.

What I want to do is automatically take the value in B20 and place it in the coloumn of my control chart. Then do another "regression" and take the new value for B20 and place it under the previous value so that my control chart updates (plots) automatically.

I have tried copy/paste special/paste link etc and also created a macro....it works for every test but when the next test (calculation) is done the previous value is replaced by the "new value". I obviously want the values stored in a coloumn with the most recent first followed by the next calculated value.

I have had a look online and it seems that the only way to do something like this is VBA code but i don't really want to learn something else (yet).....can't Excel do something as simple as storing a calculated value somehere else on the same worksheet.....and then store the next value underneath?

Any advice will be helpful......and remember I am a "newbie" and am probably overlooking something quite obvious.

Thanks,

Paul