Array Formula to find Average Return

P

Paul987

Hello

I have rows of data for which I am trying to find the average return i
%.
How can I do this correctly with an array formula? I need to take th
((exit minus the entry)/entry) (b-a)/a. for each row, then averag
that number.

I've tried to use an array {=AVERAGE(B2:B3-A2:A3)/A2:A3}
but this seems to find: (average B-average A)/ averageA
This returns a slightly different (read: wrong) answer.
Any ideas. I think there is a quick solution involving arrays, but
don't use them very often.
Thanks

Pau
 
P

Peo Sjoblom

Try

=AVERAGE((B1:B20-A1:A20)/A1:A20)

entered with ctrl + shift & enter

or


=SUMPRODUCT((B1:B20-A1:A20)/A1:A20)/ROWS(A1:B20)

=SUMPRODUCT((B1:B20-A1:A20)/A1:A20)/COUNT(B1:B20)


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com
 
Top