Selecting a Range

OK, I'm stumped. I've pulled my hair out for two hours, so I give up.
First of all thank everyone for the help.

I shifted everything to the left so the values have changed slightly.

I can't get this to work for a column of inputed data. It works fine
for a column of data derived from formulas. That's the only thing I
can think of that's holding me up. Here is the formula that works:


=IF(ISERROR(AVERAGE(IF(C15:C2498=K2,O15:O2498))),0,AVERAGE(IF(C15:C2498=K2,O15:O2498)))

This one doesn't work:

=IF(ISERROR(AVERAGE(IF(C15:C2498=K2,N15:N2498))),0,AVERAGE(IF(C15:C2498=K2,N15:N2498)))

I'm trying to do the same thing for two different columns of data.
The problem is that the second formula averages when there is no value
in the cell. So if my column is:

$10.00
$10.00
(blank cell)

I get an answer of $6.67 when I want an answer of $10.00. I want the
average of the actual values only.

Like I said the first formula does what I want no problem, and there
really shouldn't be any difference that I can see.

On Wed, 27 Aug 2008 14:20:36 +0800, "Max" <demechanik.TakeThisOut@yahoo.com>
wrote:

>To point to a cell, L2,
>which will contain the text: sar (w/o the double quotes)
>
>The 2 array-entered expressions will be simply:
>
>=AVERAGE(IF(C17:C2002=L2,O17:O2002))
>
>=IF(ISERROR(AVERAGE(IF(C17:C2002=L2,O17:O2002))),0,
>AVERAGE(IF(C17:C2002=L2,O17:O2002)))
>
>And if you have a list of text entered in L2 down (ie in L2, L3, L4, etc)
>just fix the ranges like this in your start cell, say in M2:
>
>=AVERAGE(IF(C$17:C$2002=L2,O$17:O$2002))
>
>=IF(ISERROR(AVERAGE(IF(C$17:C$2002=L2,O$17:O$2002))),0,
>AVERAGE(IF(C$17:C$2002=L2,O$17:O$2002)))
>
>Then just copy M2 down as far as required
>to return the corresponding results for each value in L2, l3, L4 etc

My guess is that you need an additional ISNUMBER check on the col N to be
averaged, to ensure that the average will be only for the cells in col N
that contains numbers (besides the concurrent check on col C = K2):

Try, array-entered:
=IF(ISERROR(AVERAGE(IF((C15:C2498=K2)*(ISNUMBER(N15:N2498)),N15:N2498))),0,AVERAGE(IF((C15:C2498=K2)*(ISNUMBER(N15:N2498)),N15:N2498)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"JimS" <jimx22.DeleteThis@msn.com> wrote in message
news:e37ab4dkq61kp67bmggig5jh492259m99i@4ax.com...
> OK, I'm stumped. I've pulled my hair out for two hours, so I give up.
> First of all thank everyone for the help.
>
> I shifted everything to the left so the values have changed slightly.
>
> I can't get this to work for a column of inputed data. It works fine
> for a column of data derived from formulas. That's the only thing I
> can think of that's holding me up. Here is the formula that works:
>
>
> =IF(ISERROR(AVERAGE(IF(C15:C2498=K2,O15:O2498))),0,AVERAGE(IF(C15:C2498=K2,O15:O2498)))
>
> This one doesn't work:
>
> =IF(ISERROR(AVERAGE(IF(C15:C2498=K2,N15:N2498))),0,AVERAGE(IF(C15:C2498=K2,N15:N2498)))
>
> I'm trying to do the same thing for two different columns of data.
> The problem is that the second formula averages when there is no value
> in the cell. So if my column is:
>
> $10.00
> $10.00
> (blank cell)
>
> I get an answer of $6.67 when I want an answer of $10.00. I want the
> average of the actual values only.
>
> Like I said the first formula does what I want no problem, and there
> really shouldn't be any difference that I can see.