deming excel function

[MI]

I have Excel 2003. I have tried running the below function i found on
the internet as an addin, but it returns an error of #value. I have
20-30 each of x and y values in columns.
Is there anything that appears wrong, or do i need to run another
function prior to running this one. I am a novice.

Function Deming(XValues, Yvalues)
Dim MeanX(), MeanY()
'Get number of cells to use in calculation loop
Ncells = XValues.Count
ReDim MeanX(Ncells / 2), MeanY(Ncells / 2)

'Step thru pairs of cells, calculating sums for statistics calcs
For x = 2 To Ncells Step 2
MeanX(x / 2) = (XValues(x - 1) + XValues(x)) / 2
MeanY(x / 2) = (Yvalues(x - 1) + Yvalues(x)) / 2
SumX = SumX + MeanX(x / 2): SumY = SumY + MeanY(x / 2)
SumX2 = SumX2 + (MeanX(x / 2)) ^ 2
SumY2 = SumY2 + (MeanY(x / 2)) ^ 2
SumXY = SumXY + MeanX(x / 2) * MeanY(x2 / 2)
SumDeltaX2 = SumDeltaX2 + (XValues(x - 1) - XValues(x)) ^ 2
SumDeltaY2 = SumDeltaY2 + (Yvalues(x - 1) - Yvalues(x)) ^ 2
Next

'Calculate some intermediate statistical quantities
XBar = SumX / N: YBar = SumY / N
Sx2 = (N * SumX2 - SumX ^ 2) / (N * (N - 1))
Sy2 = (N * SumY2 - SumY ^ 2) / (N * (N - 1))
Sdx2 = SumDeltaX2 / (2 * N)
Sdy2 = SumDeltaY2 / (2 * N)
rPearson = (N * SumXY - SumX * SumY) / _
Sqr((N * SumX2 - SumX ^ 2) * (N * SumY2 - SumY ^ 2))

'Calculate quantitites that are specific to the Deming calculation
lambda = Sdx / Sdy2
U = (Sy2 - Sx2 / lambda) / (2 * rPearson * Sqr(Sx2) * Sqr(Sy2))
Slope = U + Sqr(U ^ 2 + 1 / lambda)
Intercept = YBar - Slope * XBar

Deming = Array(Slope, Intercept)



End Function

 

[Bernie Deitrick]

I will not vouch for the math, but the code was incorrent because N was never used and you were
dividing by zero - perhaps N should have been NCells? (and lambda was always zero since it divided
an non -used variable (0) by another term. That is why you should always use option explicit to
force dimensioning of your variables - to catch typos, etc..

Anyway, try the version below to see if it returns the expected values for your data. You need to
array enter the formula (using Ctrl-Shift Enter) because the function returns an array. Select tow
cells side-by-side, type in =Deming(range1, range2), and press Ctrl-Shift-Enter.

HTH,
Bernie
MS Excel MVP


Function Deming(XValues, Yvalues)
Dim MeanX(), MeanY()
'Get number of cells to use in calculation loop
NCells = XValues.Count
ReDim MeanX(NCells / 2), MeanY(NCells / 2)

'Step thru pairs of cells, calculating sums for statistics calcs
For x = 2 To NCells Step 2
MeanX(x / 2) = (XValues(x - 1) + XValues(x)) / 2
MeanY(x / 2) = (Yvalues(x - 1) + Yvalues(x)) / 2
SumX = SumX + MeanX(x / 2): SumY = SumY + MeanY(x / 2)
SumX2 = SumX2 + (MeanX(x / 2)) ^ 2
SumY2 = SumY2 + (MeanY(x / 2)) ^ 2
SumXY = SumXY + MeanX(x / 2) * MeanY(x2 / 2)
SumDeltaX2 = SumDeltaX2 + (XValues(x - 1) - XValues(x)) ^ 2
SumDeltaY2 = SumDeltaY2 + (Yvalues(x - 1) - Yvalues(x)) ^ 2
Next

'Calculate some intermediate statistical quantities
XBar = SumX / NCells: YBar = SumY / NCells
Sx2 = (NCells * SumX2 - SumX ^ 2) / (NCells * (NCells - 1))
Sy2 = (NCells * SumY2 - SumY ^ 2) / (NCells * (NCells - 1))
Sdx2 = SumDeltaX2 / (2 * NCells)
Sdy2 = SumDeltaY2 / (2 * NCells)
rPearson = (NCells * SumXY - SumX * SumY) / _
Sqr((NCells * SumX2 - SumX ^ 2) * (NCells * SumY2 - SumY ^ 2))

'Calculate quantitites that are specific to the Deming calculation
lambda = Sdx2 / Sdy2
U = (Sy2 - Sx2 / lambda) / (2 * rPearson * Sqr(Sx2) * Sqr(Sy2))
Slope = U + Sqr(U ^ 2 + 1 / lambda)
Intercept = YBar - Slope * XBar

Deming = Array(Slope, Intercept)



End Function