averageifs with dynamic criteria

[arvan]

Hi friends!

I have a car data spreadsheet taken from a car listing website with ~7000 row entries of various cars currently on the market and only 4 columns: Make/Model, Production year, Miles and Price

I want to create a fifth column (E), where for each row I will be calculating the average market price of that specific car (based on the prices on that list), following though all the below rules

1) Model: consider prices only from cars of the exact same Make/Model
2) Production Year: consider prices from cars that have a production year from -1 to +1 (i.e. if the specific car's production year is 2012, i want to consider prices from cars between 2011 and 2013)
3) Miles: consider prices from cars with a mileage from -15000 to +15000 (i.e. if the spefic car has a mileage of 42000, i want to consider prices from cars with mileage between 27000 and 57000)
4) Avoid doublecounts (see below the example from Cintroen C5, this is a doublecount in the list

I tried to fix this in a singly formula with averageifs, but I am not able to add a dynamically running range in the function's criteria (I don't want to hard code the ranges, only the thresholds -+1 and -+15000)

Any thoughts? I am attaching also a sample view of the spreadsheet

Thanks!!!!

Make/Model	Price	Production year	Miles	Ave price based on Model-Year-Miles
Mercedes-Benz A 160	8900	2009	150000
Citroen C4	4000	2006	149980
Mercedes-Benz ML 320	25900	2008	149800
Renault Laguna	9999	2012	149500
Renault Kangoo	5150	2007	149456
Mercedes-Benz CLK 200	13990	2008	149392
Seat Cordoba	3700	2006	149142
Volkswagen Golf	8900	2006	149000
Nissan Qashqai	12500	2009	149000
Citroen C5	11800	2011	149000
Citroen C5	11800	2011	149000
Bmw 525	10950	2006	149000
Volkswagen Polo	10555	2012	149000
Opel Zafira	10500	2011	149000

<tbody>
</tbody>

 

[AlanY]

this should work except for the double counting that needed to filtered out beforehand.
but with 7000+ rows that will take ages, may be someone can provide a VBA solution

Excel 2012
	A	B	C	D	E
1	Make/Model	Price	Production year	Miles	Ave price based on Model-Year-Miles
2	Mercedes-Benz A 160	8900	2009	150000	15323
3	Mercedes-Benz A 160	4000	2006	149980	6540
4	Mercedes-Benz A 160	25900	2008	149800	13288
5	Mercedes-Benz A 160	9999	2012	149500	10931
6	Mercedes-Benz A 160	5150	2007	149456	10370
7	Mercedes-Benz A 160	13990	2008	149392	13288
8	Mercedes-Benz A 160	3700	2006	149142	6540
9	Mercedes-Benz A 160	8900	2006	149000	6540
10	Mercedes-Benz A 160	12500	2009	149000	15323
11	Mercedes-Benz A 160	11800	2011	149000	10931
12	Mercedes-Benz A 160	11800	2011	149000	10931
13	Mercedes-Benz A 160	10950	2006	149000	6540
14	Mercedes-Benz A 160	10555	2012	149000	10931
15	Mercedes-Benz A 160	10500	2011	149000	10931
Sheet1

Cell Formulas
Range	Formula
E2	{=AVERAGE(IF(($A$2:$A$15=A2)*(ABS($C$2:$C$15-C2)<=1)*(ABS($D$2:$D$15-D2)<=15000),$B$2:$B$15))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Gerald Higgins]

Two questions.
1) How do you recognise a doublecount ? Is it because all details are identical, and the two entries are consecutive ?
What would happen if you had two entries where details were identical but non-consecutive ?

2) What results do you want to see, if the data looks like this . . .
VW Golf.............10000......2006......100000
VW Golf.............12000......2007......110000
VW Golf.............15000......2008........50000
VW Golf.............11000......2006......120000
VW Golf.............20000......2009........55000
VW Golf.............22000......2010........60000
VW Golf.............18000......2008........65000

 

[arvan]

Hi Gerald Higgins,

1) It would be a doublecount if it had the exact same data across all columns

2) VW Golf.............10000......2006......100000........10000
VW Golf.............12000......2007......110000.........11000
VW Golf.............15000......2008........50000.........17666
VW Golf.............11000......2006......120000.........11500
VW Golf.............20000......2009........55000.......19000
VW Golf.............22000......2010........60000.......21000
VW Golf.............18000......2008........65000......17666

as you can see, I am not excluding the respective row

The doublecount is the only issue that I have now, AlanY's solution worked!

Thank you so much!!!

 

[AlanY]

one way to delete all the repeat data;

copy E2 all the way down, sort column E, copy F2 all the way down.
create filter on Row 1, select "Y" from the filter menu in F1, delete all rows with "Y".

Excel 2012
	A	B	C	D	E	F
1	Make/Model	Price	Production year	Miles
2	Mercedes-Benz A 160	8900	2009	150000	Mercedes-Benz A 160 2009 150000	N
3	Citroen C4	4000	2006	149980	Citroen C4 2006 149980	N
4	Mercedes-Benz ML 320	25900	2008	149800	Mercedes-Benz ML 320 2008 149800	N
5	Renault Laguna	9999	2012	149500	Renault Laguna 2012 149500	N
6	Renault Kangoo	5150	2007	149456	Renault Kangoo 2007 149456	N
7	Mercedes-Benz CLK 200	13990	2008	149392	Mercedes-Benz CLK 200 2008 149392	N
8	Seat Cordoba	3700	2006	149142	Seat Cordoba 2006 149142	N
9	Volkswagen Golf	8900	2006	149000	Volkswagen Golf 2006 149000	N
10	Nissan Qashqai	12500	2009	149000	Nissan Qashqai 2009 149000	N
11	Citroen C5	11800	2011	149000	Citroen C5 2011 149000	Y
12	Citroen C5	11800	2011	149000	Citroen C5 2011 149000	N
13	Bmw 525	10950	2006	149000	Bmw 525 2006 149000	N
14	Volkswagen Polo	10555	2012	149000	Volkswagen Polo 2012 149000	N
15	Opel Zafira	10500	2011	149000	Opel Zafira 2011 149000	N
Sheet1

Cell Formulas
Range	Formula
E2	=A2&" "&C2&" "&D2
F2	=IF(E2=E3,"Y","N")