Find Average of cells having formulas

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dale - 22 Nov 2006 03:10 GMT

How would I calculate the average of a series of cells having a formula
where the result is >0? i.e. ((G5-E5) + (S5-Q5))/ Total events
Or do I have to calculate the end result to a new cell prior to finding the
average?

Thanks for your help

Reply to this Message

bobocat - 22 Nov 2006 06:46 GMT

I am not fully understood your question,
I supposed that the total event = number of row in the series

=sum(if((g5:g20-e5:e20)+(s5:s20-q5:q20)>0,(g5:g20-e5:e20)+(s5:s20-q5:q20)))/count(s5:s20)

this is an array formula, not need to type { } in your formula, press
crtl+shift+Enter

"Dale" <dale@nospam.com> ¼¶¼g©ó¶l¥ó·s»D:%23fZPLPeDHHA.4404@TK2MSFTNGP03.phx.gbl...

> How would I calculate the average of a series of cells having a formula
> where the result is >0? i.e. ((G5-E5) + (S5-Q5))/ Total events
> Or do I have to calculate the end result to a new cell prior to finding
> the average?
>
> Thanks for your help

Reply to this Message

Bob Phillips - 22 Nov 2006 09:11 GMT

=AVERAGE((G5:G10-E5:E10)+(S5:S10-Q5:Q10))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Reply to this Message