Average if
 

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

Thanks

Average if
 

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

Average if
 

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

Average if
 

Well, that's why it's a good idea to provide as much detail as possible when
posting a question. The formula I suggested only works when each range is
the same size. So, instead of taking another guess at what you want:

Are there any negative numbers in either range?

Are there any empty cells in either range?

Biff

"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

Average if
 

If you download and install the free add-in Morefunc.xll, you can use
the following formula...

=AVERAGE(IF(SETV(ARRAY.JOIN(B9:B17,B24:B55))0,GET V()))

Alternatively, try...

=AVERAGE(IF(ISNA(MATCH(ROW(B9:B55)-ROW(B9)+1,{10,11,12,13,14,15},0)),IF(B
9:B550,B9:B55)))

....which will exclude from the average the 10th through 15th cell within
B9:B55, relative to B9. Note that both formulas needs to be confirmed
with CONTROL+SHIFT+ENTER. Also, the add-in can be download at the
following link...

http://xcell05.free.fr/

Hope this helps!

In article ,
Curtis wrote:

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

Average if
 

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

Average if
 

Sorry new to this!

Actual ranges where I need the averages are b9:b17 and b24:b53. This range
can contain zero value, which I need excluded... No negative values

However there is a range in a different column that des have zero and
negative values where I will need to average the negative values (i.e. n9:n17
and n24: n53

"Biff" wrote:

Well, that's why it's a good idea to provide as much detail as possible when
posting a question. The formula I suggested only works when each range is
the same size. So, instead of taking another guess at what you want:

Are there any negative numbers in either range?

Are there any empty cells in either range?

Biff

"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

Average if
 

Used 2nd *non-array.

Thanks Ragdyer and all

"Ragdyer" wrote:

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

Average if
 

Try Domenic's second formula. As is, it will work on your first range in
column B. You'll need to modifiy it slightly to get it to work on your other
column, column N:

Change:
0

To:
<0

Biff

"Curtis" wrote in message
...
Sorry new to this!

Actual ranges where I need the averages are b9:b17 and b24:b53. This range
can contain zero value, which I need excluded... No negative values

However there is a range in a different column that des have zero and
negative values where I will need to average the negative values (i.e.
n9:n17
and n24: n53

"Biff" wrote:

Well, that's why it's a good idea to provide as much detail as possible
when
posting a question. The formula I suggested only works when each range is
the same size. So, instead of taking another guess at what you want:

Are there any negative numbers in either range?

Are there any empty cells in either range?

Biff

"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

Average if
 

As per JMB from another thread asking exactly the same question within a few hours......

=SUM(G7:G9,G12:G15)/(SUMPRODUCT(--(G7:G9<0))+SUMPRODUCT(--(G12:G15<0)))

It is interesting that we can check for <0 when we use SUMPRODUCT, but we can't check for <0 when we use COUNTIF. This is because COUNTIF will also count null and blank.

Epinn

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

Thanks

Average if
 

Thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Curtis" wrote in message
...
Used 2nd *non-array.

Thanks Ragdyer and all

"Ragdyer" wrote:

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