CODEWITHSUNDEEP
excel
Q-bertsuit
Q-bertsuit

Reputation: 3437

Finding the max and average difference

I have a small data-set with reference values and measured values

0   0.017
5   5.2
10  11.2
15  14.4
20  20.5
25  25.5
30  31.2
35  37.6
40  40.2
42  42.8
44  43.1
46  44.7
48  47.2
50  50.3
52  53

I would like to know what the max difference and the average difference is between the reference and measured data. How is this done in Excel?

Upvotes: 0

Views: 2818

Answers (2)

Gilligan
Gilligan

Reputation: 176

You can get the Max difference using an array formula. You will need to hold Control + Shift when you press Enter after typing the array formula in the cell where you want to compute that value. If you don't hold Control + Shift while entering the formula then it won't work correctly.

For example, if you have the referenced values in A1:A15 and measured values in B1:B15, you can type =Max(Abs(B1:B15-A1:A15)) into a cell, then hold Control + Shift and press Enter to enter the formula as an array formula. That will give you the maximum difference between the values, regardless of whether the measured value is higher or lower than the referenced value.

The Average can be done the same way, just use =Average(Abs(B1:B15-A1:A15)) instead.

Upvotes: 1

xidgel
xidgel

Reputation: 3145

How about

=MAX(ABS(reference-measured))

entered as an array formula CTRL-SHIFT-ENTER

Upvotes: 0

Related Questions