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

Question:

Question: Finding multiple differences and their averages

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


15

10

12

8


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

Reply
Question marked as Solved
Answer:
Answer:

Hi nor'


Use a column to record the differences.

Find the average of that column.

User uploaded file

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

Posted on

Page content loaded

Question marked as Solved

Nov 15, 2017 7:43 PM in response to nor.support In response to nor.support

Hi nor'


Use a column to record the differences.

Find the average of that column.

User uploaded file

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

Nov 15, 2017 7:43 PM

Reply Helpful (1)
User profile for user: nor.support

Question: Finding multiple differences and their averages