Average If (I think) Help

K

KhannyX

New Member
Joined
Apr 13, 2019
Messages
2
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:
view

view

view
[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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Hi & welcome to MrExcel.
With Master data


Excel 2013/2016
ABCDEFGHI
1ForwarderOrigin CountryService Level45kg100kg300kg500kg1000kg2000kg
2Forwarder 1ArgentinaDeferred101520253035
3Forwarder 2IndonesiaStandard404550556065
4Forwarder 3United StatesStandard707580859095
Master


Result sheet


Excel 2013/2016
ABCDEFGHIJKLM
1DeferredDeferredDeferredDeferredStandardStandardStandardStandardPremiumPremiumPremiumPremium
2Origin CountryForwarder 1Forwarder 2Forwarder 3Forwarder 4Forwarder 1Forwarder 2Forwarder 3Forwarder 4Forwarder 1Forwarder 2Forwarder 3Forwarder 4
3Argentina22.5
4Indonesia52.5
5United States82.5
6Bangladesh
7Brazil
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
Fluff said:
Hi & welcome to MrExcel.
With Master data

Excel 2013/2016
ABCDEFGHI
ForwarderOrigin CountryService Level45kg100kg300kg500kg1000kg2000kg
Forwarder 1ArgentinaDeferred
Forwarder 2IndonesiaStandard
Forwarder 3United StatesStandard

<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
ABCDEFGHIJKLM
DeferredDeferredDeferredDeferredStandardStandardStandardStandardPremiumPremiumPremiumPremium
Origin CountryForwarder 1Forwarder 2Forwarder 3Forwarder 4Forwarder 1Forwarder 2Forwarder 3Forwarder 4Forwarder 1Forwarder 2Forwarder 3Forwarder 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]
Click to expand...

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!
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Rate cards
Replies
0
Views
248
MatthewPaul
M
S
  • Solved
How to calculate the Average with Condition of Networking days from the reference dates
Replies
2
Views
364
shoun2502
S
G
  • Solved
Retail - Excel Forward Cover Calculation
Replies
5
Views
978
graemestown
G
thepartydj
  • Question Question
Average Time - impossible?
Replies
2
Views
251
MountainFog
M
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
499
etaf
etaf

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top