Problem using LINEST to report polynomial coefficients

Hi guys.

I am using the LINEST function to calculate the coefficients of a 2nd order polynomial relationship between 2 sets of variables i.e. LINEST(Y data, X data ^{1;2}). There are 8 values in total for the range of data and the values for X are always the same. The problem is Excel only reports these coefficients when a value is present in the Y range for all values of X. If there is a value missing, which there often is because we do always run the experiment using each of the X values, then #VALUE is reported for each of the 3 coefficients.

Does anyone know a way around this?

Many thanks
Tim

Hi,

Please upload a sample template of your workbook here.

Hi, have attached a template. The X axis range in B2:I2 and the Y range is B3:I3. Cell Q3 calculates the concentration of the parameter based on the measured value given in cell O3 and the coefficients in cells K3:M3.

If one of the values is removed from the Y range, #VALUE! appears in the cells containing formula.

Thanks
Tim

If anyone comes across this thread needing a solution to this problem, the following link will provide the answers:

http://newtonexcelbach.wordpress.com...ata-with-gaps/