Average If (I think) Help

[KhannyX]

Hey guys,

Long time lurker, first time poster!

Need some help if I may, I'm running a dataset for tender airfreight costs for my business, got the results split by country of origin, by weight break (45kg,100kg etc), across 4 carriers and three service levels (Deferred/Standard and Premium)

I want to return an average cost by country for each service level, by carrier, so I can model that onto my forecast numbers for the 2019 year.

Data looks a bit like this:

Forwarder	Origin Country	Service Level	45kg	100kg	300kg	500kg	1000kg	2000kg
Forwarder 1	Argentina	Deferred	Value 1	Value 2	Value 3	Value 4	Value 5	Value 6
Forwarder 2	Indonesia	Standard	Value 1	Value 2	Value 3	Value 4	Value 5	Value 6
Forwarder 3	United States	Standard	Value 1	Value 2	Value 3	Value 4	Value 5	Value 6

<tbody>
</tbody>

What I need to do is get an average by carrier by country, across the weight breaks, hopefully to build an averages table that I can lookup into...open to suggestions on format but I have in mind:

Deferred

Standard

Premium

Origin Country

Forwarder 1

Forwarder 2

Forwarder 3

Forwarder 4

Forwarder 1

Forwarder 2

Forwarder 3

Forwarder 4

Forwarder 1

Forwarder 2

Forwarder 3

Forwarder 4

Argentina

Australia

Bangladeah

Bangladesh

Brazil

<tbody>
</tbody>

What can I do to return that average number over those breaks by carrier? I was thinking of an averageifs, but cant seem to get my head around it! I was also thinking it may be an array index match match, but again I want to return the average across weight breaks and the match match wont do that...

Any help appreciated, I'm using latest excel (Office 365).

Please and thank you!

 

[Fluff]

Hi & welcome to MrExcel.
With Master data

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	Forwarder	Origin Country	Service Level	45kg	100kg	300kg	500kg	1000kg	2000kg
2	Forwarder 1	Argentina	Deferred	10	15	20	25	30	35
3	Forwarder 2	Indonesia	Standard	40	45	50	55	60	65
4	Forwarder 3	United States	Standard	70	75	80	85	90	95
Master

Result sheet

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Deferred	Deferred	Deferred	Deferred	Standard	Standard	Standard	Standard	Premium	Premium	Premium	Premium
2	Origin Country	Forwarder 1	Forwarder 2	Forwarder 3	Forwarder 4	Forwarder 1	Forwarder 2	Forwarder 3	Forwarder 4	Forwarder 1	Forwarder 2	Forwarder 3	Forwarder 4
3	Argentina	22.5
4	Indonesia						52.5
5	United States							82.5
6	Bangladesh
7	Brazil
Sheet2

Cell Formulas
Range	Formula
B3	{=IFERROR(AVERAGE(INDEX(Master!$D$2:$I$4,MATCH(1,(Master!$A$2:$A$4=B$2)*(Master!$B$2:$B$4=$A3)*(Master!$C$2:$C$4=B$1),0),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[KhannyX]

Hi & welcome to MrExcel.
With Master data

Excel 2013/2016

	A	B	C	D	E	F	G	H	I
1	Forwarder	Origin Country	Service Level	45kg	100kg	300kg	500kg	1000kg	2000kg
2	Forwarder 1	Argentina	Deferred	10	15	20	25	30	35
3	Forwarder 2	Indonesia	Standard	40	45	50	55	60	65
4	Forwarder 3	United States	Standard	70	75	80	85	90	95

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Master​

Result sheet

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Deferred	Deferred	Deferred	Deferred	Standard	Standard	Standard	Standard	Premium	Premium	Premium	Premium
2	Origin Country	Forwarder 1	Forwarder 2	Forwarder 3	Forwarder 4	Forwarder 1	Forwarder 2	Forwarder 3	Forwarder 4	Forwarder 1	Forwarder 2	Forwarder 3	Forwarder 4
3	Argentina	22.5
4	Indonesia						52.5
5	United States							82.5
6	Bangladesh
7	Brazil

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula B3 {=IFERROR(AVERAGE(INDEX(Master!$D$2:$I$4,MATCH(1,(Master!$A$2:$A$4=B$2)*(Master!$B$2:$B$4=$A3)*(Master!$C$2:$C$4=B$1),0),0)),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

That is absolutely perfect!

I think I've been sat in front of it for too long today and started confusing myself!

Thank you so much Fluff, means I can get onto something else this evening now I've managed to get that done and formatted

Have a fantastic day!

 

[Fluff]

Glad to help & thanks for the feedback