Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Level 1

4 points

Finding multiple differences and their averages

Ok so let’s say I’ve got one table with a column of

How can I find the differences between each row and it’s neighbor and automatically send the average of all the differences into a separate box in another table? Also this column is likely to get really long since I will be recording data points for several months.

basically do

15 (difference between 15 and 10 is 5)

10 (difference between 10 and 12 is 2)

12 (difference between 12 and 8 is 4)

8 (the average of 5, 2, and 4, is 3.66.....)

but all with formula?

Thank you for your help!

iPad Pro Wi-Fi, iOS 11.1

Posted on Nov 15, 2017 6:44 PM

Best reply

Barry

Level 9

59,861 points

Posted on Nov 15, 2017 7:43 PM

Hi nor'

Use a column to record the differences.

Find the average of that column.

Formulas:

B3 (and filled down):

B3: IF(AND(LEN(A2)>0,LEN(A3)>0),ABS(A2−A3),"")

C2: AVERAGE(B)

The formula in column B checks the cels in column a one row above its row and on its row. If both contain a value, the diference is calculated, and ABS converts that to an absolute (positive) value. If either test cell is empty, the formula returns a null string, which appears 'blank' and is not included in the 'AVERAGE' calculations.

C2: Returns the mean of all numeric values in column B, an ignores the text values (including the null strings ( "" ) returned by the formula in column B).

Regards,

Barry

View in context

1 reply

There are no replies.

Finding multiple differences and their averages