Advanced Filtering - Computed Criteria

[KIM]

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

 

[Jason Morin]

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

 

[KIM]

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!

 

[Aladin Akyurek]

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

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

 

[bj]

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.

 

[bj]

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.