Averaging two ranges on the same column (excluding zeros)
 

Hello - I have a range of numbers (formatted as %) that I am trying to
average. All the values are on one row. However, the numbers are broken into
two ranges, since took two separate averages of the sub-totals using Excel
2007's "AverageIF" function.

My problem is that I want to average the two ranges that appear on the same
column, but since one of the cells contains the sub-total, I cannot use a
contigous range. I need to average two ranges, excluding zero values.

Essentially, something like this:
=AVERAGEIF(A1:A24,"<0")
AND
=AVERAGEIF(A26:A30,"<0")


Please assist.

Averaging two ranges on the same column (excluding zeros)
 

Essentially, something like this:
=AVERAGEIF(A1:A24,"<0")
AND
=AVERAGEIF(A26:A30,"<0")

So, what's in A25? Your subtotal?

Try this:

=(SUM(A1:A30)-A25)/(COUNTIF(A1:A24,"<0")+COUNTIF(A26:A30,"<0"))


--
Biff
Microsoft Excel MVP


"NoviceUser" wrote in message
...
Hello - I have a range of numbers (formatted as %) that I am trying to
average. All the values are on one row. However, the numbers are broken
into
two ranges, since took two separate averages of the sub-totals using Excel
2007's "AverageIF" function.

My problem is that I want to average the two ranges that appear on the
same
column, but since one of the cells contains the sub-total, I cannot use a
contigous range. I need to average two ranges, excluding zero values.

Essentially, something like this:
=AVERAGEIF(A1:A24,"<0")
AND
=AVERAGEIF(A26:A30,"<0")


Please assist.

Averaging two ranges on the same column (excluding zeros)
 

"data" is a defined name range A1:A30 no quotes

=AVERAGE(IF((ROW(data)<ROW(A25))*(data<0),data))

ctrl+shift+enter, not just enter


"NoviceUser" wrote:

Hello - I have a range of numbers (formatted as %) that I am trying to
average. All the values are on one row. However, the numbers are broken into
two ranges, since took two separate averages of the sub-totals using Excel
2007's "AverageIF" function.

My problem is that I want to average the two ranges that appear on the same
column, but since one of the cells contains the sub-total, I cannot use a
contigous range. I need to average two ranges, excluding zero values.

Essentially, something like this:
=AVERAGEIF(A1:A24,"<0")
AND
=AVERAGEIF(A26:A30,"<0")


Please assist.