Running Average Ratio Formula?

[RalphSE]

Hi,

I have a column of numbers, some positive, some negative, some zero.
need a formula that will count all the positive numbers and take th
average of them, then count all the negative numbers and take th
average of those, and then divide the average of the positive number
by the average of the negative numbers.

thanks!

 

[Dav]

if range is the data you are interested in eg a1:a50, substitut
accordingly

countif(range,"<0") will count the negative numbers
countif(range,">0") will count the positive numbers
sumif(range,"<0") will sum the negative numbers
sumif(range,">0") will sum the positive numbers

so if you combine them

=(sumif(range,">0") /countif(range,">0") )/(sumif(range,"<0"
/countif(range,"<0") )

The answer will alway be negative if you wish it to be positive put
minus in front!. You have also not said what to do with 0 values unles
they do not occur. The < statements may need to be adjusted accordingly

Regards

Da

 

[Domenic]

Try the following formulas which need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

For positive numbers...

B1:

=AVERAGE(IF(A1:A100>0,A1:A100))

For negative numbers...

C1:

=AVERAGE(IF(A1:A100<0,A1:A100))

For your ratio...

D1:

=B1/C1

Hope this helps!

 

[RalphSE]

THANKS DAVE but i dont understand how to use your formula, the range i
R2 to R102, can you adjust the formula to work for those cells?
tried my best but failed, miserably, LO

 

[Dav]

=(SUMIF($R$2:$R$102,">0")/COUNTIF($R$2:$R$102,">0"))/((SUMIF($R$2:$R$102,"<0")/COUNTIF($R$2:$R$102,"<0")))

But Domenic's solution is shorter and gives the same result

=AVERAGE(IF(R2:R102>0,R2:R102))/AVERAGE(IF(R2:R102<0,R2:R102))

when you have entered the formula as he says press CONTROL+SHIFT+ENTER
not just ENTER..

both formulas do work though!

Da

 

[RalphSE]

Thanks Fellas, I'm In Business!! :