average of subtractions

**bhowell** · 02-02-2010, 04:00 PM

Hello,

This is a simplified example, but using this table I would like to find the average of their subtractions.

A B
4 1
3 1
2 1
1 1

I am looking for a single cell formula that would do "=average(4 - 1, 3 - 1, 2 - 1, 1 - 1)"

What I have been doing up to this point is creating another column of subtractions and then averaging that, but I would like to get rid of this extra column (which actually ends up being several columns with all my data) and replace it with a single cell formula is possible.

Thanks for the help,
Boyd

Excel 2003, Windows XP

**DonkeyOte** · 02-02-2010, 04:05 PM

You could use:

=SUMPRODUCT(A1:A4-B1:B4)/COUNT(A1:A4)

or

=AVERAGE(A1:A4-B1:B4)
confirmed with CTRL + SHIFT + ENTER

EDIT:

you could also perhaps just use:

=(SUM(A1:A4)-SUM(B1:B4))/COUNT(A1:A4)

**shg** · 02-02-2010, 04:09 PM

Or you could use =AVERAGE(A1:A4-B1:B4), array entered.

Or =AVERAGE(A1:A4) - AVERAGE(B1:B4), normally entered.

**bhowell** · 02-03-2010, 12:36 PM

Wow, I have been out of school for too long.

It completely didn't pass my mind that

( (A1-B1)+(A2-B2)+(A3-B3)+(A4-B4) ) / 4

And

( (A1+A2+A3+A4) / 4 ) - ( (B1+B2+B3+B4) / 4 )

are actually the same thing.

Thanks for the help on this. As a side note, we also were looking to possibly find the min/max values of:

A1-B1
A2-B2
A3-B3
A4-B4

I discovered that using the formula from DonkeyOte:

=AVERAGE(A1:A4-B1:B4)
confirmed with CTRL + SHIFT + ENTER

Replacing average with min/max, it also works. I have been using excel for years and I never knew about the CTRL+SHIFT+ENTER / curly brackets option. I'm going to go read up on how that works.

Thanks again everyone!