Find Average of Mileage Bands

[nniedzielski]

I have the following three cells, Column M is a formula that finds mileage bands based off column K.

I have a column M that has cost per mile, I want to create column N as "Mileage Band Average" that will check Column L, see what the mileage band is, then give me the Average of Column M that meets that criteria. I keep getting the correct answer in N2 when using AverageIFS, but it fails when i copy down. this is the formula im trying:

=AVERAGEIFS(M:M,L:L,"81-100",M:M,"<121")

 

[awoohaw]

can you post the data in a table or use the xl2bb add in so that forum users do not have to recreate the data?

 

[nniedzielski]

im very sorry

DISTANCE_IN_MILES	Mileage Band	Cost per Mile
1672​	1501-2000	$ 2.01
1672​	1501-2000	$ 2.01
935​	501-1000	$ 2.16
935​	501-1000	$ 2.16
935​	501-1000	$ 2.16
87​	0-100	$ 0.62
87​	0-100	$ 0.62
289​	201-500	$ 2.12
0​	0-100	$ -
640​	501-1000	$ 3.20
289​	201-500	$ 2.12
640​	501-1000	$ 3.20
0​	0-100	$ -
289​	201-500	$ 2.12
640​	501-1000	$ 3.20
0​	0-100	$ -
277​	201-500	$ 3.10
640​	501-1000	$ 3.20
130​	121-140	$ 3.93
640​	501-1000	$ 3.20

 

[awoohaw]

Here is something, but i'm not sure it is what you want. Unless you want average per trip and not a weighted average per mile

Mr Excel Questions 9.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1											Distance	Mileage	Cost Per Mile
2											87	81-100	0.62	0.62
3											87	81-100	0.62	0.62
4											62	61-80	2.42	2.383333
5											62	61-80	2.42	2.383333
6											12	0-40	4.82	5.497
7											37	0-40	3.38	5.497
8											37	0-40	3.38	5.497
9											37	0-40	3.38	5.497
10											37	0-40	3.38	5.497
11											51	41-60	13.41	13.41
12											51	41-60	13.41	13.41
13											51	41-60	13.41	13.41
14											51	41-60	13.41	13.41
15											51	41-60	13.41	13.41
16											37	0-40	3.38	5.497
17											65	61-80	2.31	2.383333
18											36	0-40	3.47	5.497
19											16	0-40	10.57	5.497
20											28	0-40	6.4	5.497
21											14	0-40	12.81	5.497
Sheet3

Cell Formulas
Range	Formula
N2:N21	N2	=AVERAGEIF($L$2:$L$21,L2,$M$2:$M$21)

 

[awoohaw]

Here is the above based on your copy/paste data:

Mr Excel Questions 9.xlsm
	K	L	M	N
23	DISTANCE_IN_MILES	Mileage Band	Cost per Mile
24	1672	1501-2000	$ 2.01	2.01
25	1672	1501-2000	$ 2.01	2.01
26	935	501-1000	$ 2.16	2.81
27	935	501-1000	$ 2.16	2.81
28	935	501-1000	$ 2.16	2.81
29	87	0-100	$ 0.62	0.62
30	87	0-100	$ 0.62	0.62
31	289	201-500	$ 2.12	2.365
32	0	0-100	$ -	0.62
33	640	501-1000	$ 3.20	2.81
34	289	201-500	$ 2.12	2.365
35	640	501-1000	$ 3.20	2.81
36	0	0-100	$ -	0.62
37	289	201-500	$ 2.12	2.365
38	640	501-1000	$ 3.20	2.81
39	0	0-100	$ -	0.62
40	277	201-500	$ 3.10	2.365
41	640	501-1000	$ 3.20	2.81
42	130	121-140	$ 3.93	3.93
43	640	501-1000	$ 3.20	2.81
Sheet3

Cell Formulas
Range	Formula
N24:N43	N24	=AVERAGEIF($L$24:$L$43,L24,$M$24:$M$43)