Running Average Ratio Formula?

[RalphSE]

Hi,

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

thanks!!

[Dav]

if range is the data you are interested in eg a1:a50, substitute 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 a minus in front!. You have also not said what to do with 0 values unless they do not occur. The < statements may need to be adjusted accordingly

Regards

Dav

[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!

In article <[email protected]>,
RalphSE <[email protected]> wrote:

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

[RalphSE]

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

[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!

Dav

[RalphSE]

Thanks Fellas, I'm In Business!!