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:

[TABLE="width: 590"]
<tbody>[TR]
[TD]Forwarder[/TD]
[TD]Origin Country[/TD]
[TD]Service Level[/TD]
[TD]45kg[/TD]
[TD]100kg[/TD]
[TD]300kg[/TD]
[TD]500kg[/TD]
[TD]1000kg[/TD]
[TD]2000kg[/TD]
[/TR]
[TR]
[TD]Forwarder 1[/TD]
[TD]Argentina[/TD]
[TD]Deferred[/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[TD]Value 3[/TD]
[TD]Value 4[/TD]
[TD]Value 5[/TD]
[TD]Value 6[/TD]
[/TR]
[TR]
[TD]Forwarder 2[/TD]
[TD]Indonesia[/TD]
[TD]Standard[/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[TD]Value 3[/TD]
[TD]Value 4[/TD]
[TD]Value 5[/TD]
[TD]Value 6[/TD]
[/TR]
[TR]
[TD]Forwarder 3[/TD]
[TD]United States[/TD]
[TD]Standard[/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[TD]Value 3[/TD]
[TD]Value 4[/TD]
[TD]Value 5[/TD]
[TD]Value 6[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 1079"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 4"]Deferred[/TD]
[TD="colspan: 4"]Standard[/TD]
[TD="colspan: 4"]Premium[/TD]
[/TR]
[TR]
[TD]Origin Country[/TD]
[TD]Forwarder 1[/TD]
[TD]Forwarder 2[/TD]
[TD]Forwarder 3[/TD]
[TD]Forwarder 4[/TD]
[TD]Forwarder 1[/TD]
[TD]Forwarder 2[/TD]
[TD]Forwarder 3[/TD]
[TD]Forwarder 4[/TD]
[TD]Forwarder 1[/TD]
[TD]Forwarder 2[/TD]
[TD]Forwarder 3[/TD]
[TD]Forwarder 4[/TD]
[/TR]
[TR]
[TD]Argentina[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Australia[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bangladeah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bangladesh[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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
Forwarder	Origin Country	Service Level	45kg	100kg	300kg	500kg	1000kg	2000kg
Forwarder 1	Argentina	Deferred
Forwarder 2	Indonesia	Standard
Forwarder 3	United States	Standard

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]35[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]65[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]95[/TD]

</tbody>

Master​

Result sheet

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	J	K	L	M
Deferred	Deferred	Deferred	Deferred	Standard	Standard	Standard	Standard	Premium	Premium	Premium	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
Indonesia
United States
Bangladesh
Brazil

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]22.5[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]52.5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]82.5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]{=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)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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