You have *uneven* range sizes!

You could try this *non-array* formula if you *don't* have negative values:

=SUM(B9:B17,B24:B55)/(COUNTIF(B9:B17,"0")+COUNTIF(B24:B55,"0"))

If you *do* have negatives, try this *non-array* formula :

=SUM(B9:B17,B24:B55)/SUM(COUNTIF(B9:B17,{"0","<0"})+COUNTIF(B24:B55,{" 0","
<0"}))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Curtis" wrote in message
...
Thanks

But when I use the formula


{=AVERAGE(IF(CHOOSE({1,2},B9:B17,B24:B55)<0,CHOOS E({1,2},B9:B17,B24:B55)))}

it returns a value of #N/A

What am I doing wrong?

"Biff" wrote:

Hi!

Is this what you mean?

Average A1:A5 and F1:F5?

If so, try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not
just
ENTER):

=AVERAGE(IF(CHOOSE({1,2},A1:A5,F1:F5)<0,CHOOSE({1 ,2},A1:A5,F1:F5)))

Biff

"Curtis" wrote in message
...
How do I calculate the averages of numbers in 2 sets of ranges...
excluding
zero vaules?

Thanks