Advanced Filtering - Computed Criteria

I an trying to filter a table that contains house prices. I would like to
filter to show the records where the list price is less than the average list
price of all records.

I am using the formula =ListPrice>AVERAGE(C:C) but I keep getting a #DIV/0!
error even if I specify the actually range of my data e.g.
=ListPrice>AVERAGE(C2:C50).

Any ideas?

Thank you

I'm not sure what ListPrice refers to, but try:

=C2>AVERAGE(C:C)

in a cell (say J2), and use J1:J2 as your criteria range,
where J1 is empty.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I an trying to filter a table that contains house
prices. I would like to
>filter to show the records where the list price is less
than the average list
>price of all records.
>
>I am using the formula =ListPrice>AVERAGE(C:C) but I
keep getting a #DIV/0!
>error even if I specify the actually range of my data
e.g.
>=ListPrice>AVERAGE(C2:C50).
>
>Any ideas?
>
>Thank you
>.
>

Thank you I had tried that.

I have just re-entered (manually) all the data into a new column and that
seems to have corrected it. The information was in currency and had been
copied from different file so for some reason the cell format wasn't correct
and although I change the cell format this did not correct the problem -
hence I re-typed the data into a new column and now it seems to work.

Thank you for your suggestion though!

"Jason Morin" wrote:

> I'm not sure what ListPrice refers to, but try:
>
> =C2>AVERAGE(C:C)
>
> in a cell (say J2), and use J1:J2 as your criteria range,
> where J1 is empty.
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >I an trying to filter a table that contains house
> prices. I would like to
> >filter to show the records where the list price is less
> than the average list
> >price of all records.
> >
> >I am using the formula =ListPrice>AVERAGE(C:C) but I
> keep getting a #DIV/0!
> >error even if I specify the actually range of my data
> e.g.
> >=ListPrice>AVERAGE(C2:C50).
> >
> >Any ideas?
> >
> >Thank you
> >.
> >
>

You need to lock the range AVERAGE is applied to...

=ListPrice>AVERAGE($C$2:$C$50)

KIM wrote:
> I an trying to filter a table that contains house prices. I would like to
> filter to show the records where the list price is less than the average list
> price of all records.
>
> I am using the formula =ListPrice>AVERAGE(C:C) but I keep getting a #DIV/0!
> error even if I specify the actually range of my data e.g.
> =ListPrice>AVERAGE(C2:C50).
>
> Any ideas?
>
> Thank you

ypour problem appears to be that it it taking the Dollar quantitiees as text
=ListPrice>AVERAGE(value(C$2:C$50)) and enter as an array (control shift
enter) and it should calculate the actual average.

"KIM" wrote:

> I an trying to filter a table that contains house prices. I would like to
> filter to show the records where the list price is less than the average list
> price of all records.
>
> I am using the formula =ListPrice>AVERAGE(C:C) but I keep getting a #DIV/0!
> error even if I specify the actually range of my data e.g.
> =ListPrice>AVERAGE(C2:C50).
>
> Any ideas?
>
> Thank you

I meant to use
=value(ListPrice)>AVERAGE(value(C$2:C$50)) and entered as an array (control
shift
assuming that the list price is in the c column and in the same format as
the others.
"bj" wrote:

> ypour problem appears to be that it it taking the Dollar quantitiees as text
> =ListPrice>AVERAGE(value(C$2:C$50)) and enter as an array (control shift
> enter) and it should calculate the actual average.
>
> "KIM" wrote:
>
> > I an trying to filter a table that contains house prices. I would like to
> > filter to show the records where the list price is less than the average list
> > price of all records.
> >
> > I am using the formula =ListPrice>AVERAGE(C:C) but I keep getting a #DIV/0!
> > error even if I specify the actually range of my data e.g.
> > =ListPrice>AVERAGE(C2:C50).
> >
> > Any ideas?
> >
> > Thank you