Array Formula to find Average Return
 

Hello

I have rows of data for which I am trying to find the average return in
%.
How can I do this correctly with an array formula? I need to take the
((exit minus the entry)/entry) (b-a)/a. for each row, then average
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 I
don't use them very often.
Thanks

Paul


--
Paul987
------------------------------------------------------------------------
Paul987's Profile: http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=540360

Array Formula to find Average Return
 

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


"Paul987" wrote in
message ...

Hello

I have rows of data for which I am trying to find the average return in
%.
How can I do this correctly with an array formula? I need to take the
((exit minus the entry)/entry) (b-a)/a. for each row, then average
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 I
don't use them very often.
Thanks

Paul


--
Paul987
------------------------------------------------------------------------
Paul987's Profile:
http://www.excelforum.com/member.php...o&userid=24850
View this thread: http://www.excelforum.com/showthread...hreadid=540360