Average if

[Guest]

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

Thanks

 

[Biff]

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

 

[Guest]

Thanks

But when I use the formula

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

it returns a value of #N/A

What am I doing wrong?

 

[Biff]

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

 

[Domenic]

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,GETV()))

Alternatively, try...

=AVERAGE(IF(ISNA(MATCH(ROW(B9:B55)-ROW(B9)+1,{10,11,12,13,14,15},0)),IF(B
9:B55>0,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!

 

[Ragdyer]

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"}))

 

[Guest]

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

 

[Guest]

Used 2nd *non-array.

Thanks Ragdyer and all

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"}))

 

[Biff]

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

 

[Epinn]

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

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

Thanks

 

[RagDyer]

Thanks for the feed-back.