Average of difference between columns

[magicalmarshmallow]

Hey all,

I'm not sure if this is possible, but it would be brilliant if I could do it.

I have 2 columns of data running from A50:A1967 and B50:B1967.

What I want to do is write a single formula, in Cell C1, which will evaluate the difference between columns A and B, then take an average of this value.

A long winded version of this would be to write in column C =(B50-A50), Drag. Then in Cell D1, write Average(C50:C1967).

Clearly a one cell formula would be much neater. Can this be done?

Please and thanks

[Jonmo1]

You can try this array formula entered with CTRL + SHIFT + ENTER

=AVERAGE(B50:B1967-A50:A1967)


Or without the array entry..

=SUMPRODUCT((B50:B1967-A50:A1967)/COUNT(A50:A1967))