In column A I have the following numbers:
1, 4, 7
In column B I have the following numbers:
3, 1, 9
I want to find the average difference between these numbers in the following fashion:
=ABS(A-B)
Eg.
ABS(1-3) = 2
ABS(4-1) = 3
ABS(7-9)= 2
Then:
AVERAGE(2,3,2) = 2.33
However if I were to do this:
=ABS(AVERAGE(A:A)-AVERAGE(B:B))
The result given would be 0.333, which is not what I want.
Is there a formula that will achieve the 2.33 answer that can be used in one cell, not needing to put the ABS(A-B) in a separate column? I ask, because I will need to use this on a much larger data set.
If not, I will create the helper column. Thanks.
1, 4, 7
In column B I have the following numbers:
3, 1, 9
I want to find the average difference between these numbers in the following fashion:
=ABS(A-B)
Eg.
ABS(1-3) = 2
ABS(4-1) = 3
ABS(7-9)= 2
Then:
AVERAGE(2,3,2) = 2.33
However if I were to do this:
=ABS(AVERAGE(A:A)-AVERAGE(B:B))
The result given would be 0.333, which is not what I want.
Is there a formula that will achieve the 2.33 answer that can be used in one cell, not needing to put the ABS(A-B) in a separate column? I ask, because I will need to use this on a much larger data set.
If not, I will create the helper column. Thanks.