Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 =ListPriceAVERAGE(C:C) but I keep getting a #DIV/0! error even if I specify the actually range of my data e.g. =ListPriceAVERAGE(C2:C50). Any ideas? Thank you |
#2
|
|||
|
|||
I'm not sure what ListPrice refers to, but try:
=C2AVERAGE(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 =ListPriceAVERAGE(C:C) but I keep getting a #DIV/0! error even if I specify the actually range of my data e.g. =ListPriceAVERAGE(C2:C50). Any ideas? Thank you . |
#3
|
|||
|
|||
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: =C2AVERAGE(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 =ListPriceAVERAGE(C:C) but I keep getting a #DIV/0! error even if I specify the actually range of my data e.g. =ListPriceAVERAGE(C2:C50). Any ideas? Thank you . |
#4
|
|||
|
|||
You need to lock the range AVERAGE is applied to...
=ListPriceAVERAGE($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 =ListPriceAVERAGE(C:C) but I keep getting a #DIV/0! error even if I specify the actually range of my data e.g. =ListPriceAVERAGE(C2:C50). Any ideas? Thank you |
#5
|
|||
|
|||
ypour problem appears to be that it it taking the Dollar quantitiees as text
=ListPriceAVERAGE(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 =ListPriceAVERAGE(C:C) but I keep getting a #DIV/0! error even if I specify the actually range of my data e.g. =ListPriceAVERAGE(C2:C50). Any ideas? Thank you |
#6
|
|||
|
|||
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 =ListPriceAVERAGE(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 =ListPriceAVERAGE(C:C) but I keep getting a #DIV/0! error even if I specify the actually range of my data e.g. =ListPriceAVERAGE(C2:C50). Any ideas? Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I get the mode, min, and max with multiple criteria? | Excel Discussion (Misc queries) | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Case sensitive advanced filtering | Excel Discussion (Misc queries) | |||
Advanced Filter using Date represented as text | Excel Worksheet Functions | |||
extracting numbers with no more than 8-digits using advanced filtering | Excel Worksheet Functions |