Average the difference of 2 columns w/out a 3rd column

scrandal

Board Regular
Joined
Jul 15, 2004
Messages
160
I have two columns:

A B

1 10
2 9
3 8
4 7
5 6
6 5
7 4
8 3
9 2
10 1

I need a formula that takes the difference of the two columns (1-10, 2-9, etc.) and then finds the average for all of the differences. I don't want to have a third column with the differences and then take the average of those. Any help would be much appreciated.

Thanks,
Steve
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
=AVERAGE(A1:A10-B1:B10)

which needs to be confirmed with control+shift+enter, not just with enter.
 

dcardno

Well-known Member
Joined
Nov 20, 2002
Messages
543
Office Version
  1. 2013
Platform
  1. Windows
The average of the differences will be equal to the difference of the averages, so
Code:
=AVERAGE(A1:A10) - AVERAGE(B1:B10)
will also work, as will the difference in the sums divided by the number of rows of data. The only advantage is that they are not array formulas, and so don't need to be confirmed with Crtl-Shift-Enter (and may be easier for some users to follow)...
 

scrandal

Board Regular
Joined
Jul 15, 2004
Messages
160

ADVERTISEMENT

I have a similar question to the one I asked earlier in this post.

Once again I have an array of data starting in A1:

1
2
3
4
5
6
7
8
9
10

Starting in cell B3 I have the formula =sum(A1:A3). I have filled this down to cell B10. Below are the results (Starting in B3):

6
9
12
15
18
21
24
27

Then in A11 I take an average of B3:B10. Is there a way to do the calculation in A11 without first having to do the intermediate step of suming the cells in column B? Thanks.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,700
Try...

=AVERAGE(SUBTOTAL(9,OFFSET(A1:A10,ROW(INDIRECT("1:"&ROWS(A1:A10)-2))-1,,3)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 

scrandal

Board Regular
Joined
Jul 15, 2004
Messages
160

ADVERTISEMENT

That works perfectly. Thanks.
 

scrandal

Board Regular
Joined
Jul 15, 2004
Messages
160
Let's see if we can take this a step further. Below are my columns of data (Starting in A1):

A B C

1 5 25
2 7 24
3 9 23
4 11 22
5 13 21
6 15 20
7 17 19
8 19 18
9 21 17
10 23 16

Starting in column D3 I have the formula =Sum((.5*(A1:A4))+(.25*(B1:B4))+(.25*(C1:C4))). I have filled this formula down to D10. Then I take an average of D3:D10 and get 12.5. Is there a way to get the result of 12.5 without the intermediate step of the calculations in column D?
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,700
Shouldn't the average be 34.125? If this is correct, try...

=AVERAGE(MMULT(SUBTOTAL(9,OFFSET(A1:C10,ROW(INDIRECT("1:"&ROWS(A1:C10)-2))-1,{0,1,2},3,1))*{0.5,0.25,0.25},{1;1;1}))

or

=AVERAGE(MMULT(SUBTOTAL(9,OFFSET(A1:C10,ROW(INDIRECT("1:"&ROWS(A1:C10)-2))-1,COLUMN(A1:C10)-COLUMN(A1),3,1))*{0.5,0.25,0.25},TRANSPOSE(COLUMN(A1:C10)^0)))

Note that the second formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!
 

scrandal

Board Regular
Joined
Jul 15, 2004
Messages
160
Thanks for the quick response. My average was wrong, but I get 12.13. With this formula in D3 "=SUM((0.5*(A1:A3)),(0.25*(B1:B3)),(0.25*(C1:C3)))" and I drag it down to D10 I get these values:

D3=9.5
D4=10.25
D5=11
D6=11.75
D7=12.5
D8=13.25
D9=14
D10=14.75

When I take the average of them I get 12.13, any ideas?
 

Forum statistics

Threads
1,136,878
Messages
5,678,300
Members
419,753
Latest member
Sallylwy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top