adding fileds to an excel database using vairable criteria
 

I'm using excel 2003 as a database.

The db has 10 or so fields (i.e columns) and just under 1000 records
(i.e.rows)

for argument sakes, lets say they are product prices for various brands i
many cities.

I need to add averages of the prices using various criteria to each record.
So for example I need to add the average price for all cars except the Ford
brand in Los Angeles just to the los Angeles records.

I tried to use the DAVERGE() func and copy it down the column but the
criteria needs to be "fairly" static. Is there a way to use IF or maybe an
array {} func to do this. Something that looks at the entire db and if the
city matches the city on that row, then average all the prices for the except
except for Ford.

I can do it easily with a pivot table and then link with a VLOOKUP but I
have many many of these db's and it starts to get complicated.

adding fileds to an excel database using vairable criteria
 

SOLVED- using:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

this is a great func to know for a variety of db, filtering and conditional
operations

"westcoastr" wrote:

I'm using excel 2003 as a database.

The db has 10 or so fields (i.e columns) and just under 1000 records
(i.e.rows)

for argument sakes, lets say they are product prices for various brands i
many cities.

I need to add averages of the prices using various criteria to each record.
So for example I need to add the average price for all cars except the Ford
brand in Los Angeles just to the los Angeles records.

I tried to use the DAVERGE() func and copy it down the column but the
criteria needs to be "fairly" static. Is there a way to use IF or maybe an
array {} func to do this. Something that looks at the entire db and if the
city matches the city on that row, then average all the prices for the except
except for Ford.

I can do it easily with a pivot table and then link with a VLOOKUP but I
have many many of these db's and it starts to get complicated.