Steal This Excel Formula To Quickly Compare Small Parcel Rates

Comparing new parcel carrier rates is time consuming when done manually.?

Why??

They’re two dimensional, based on Zone and Weight.?

Something like this…

In a shipment history file (aka Package Level Detail), you have the following:

- Ship to Zone

- Weight

- Current Carrier Rate

Comparing rates on 7 shipments is easy.?

But what if you have 10’s or 100’s of thousands of shipments in your shipment history file?

A few years ago, I finally solved it. Was I excited as ever when I figured out how to do it using one Excel formula. It also works with Google Spreadsheets.

So go ahead… steal my formula below, and you’re sure to save time and impress your colleagues.

First, please note, there are different ways to do this. I found this works well for me.

Step 1)?

Open your shipment history file.?

Make sure the Weight is referenceable.?

- If the weight in the shipment history file is 10.5 lbs, round it up to 11 so that it’s referenceable. Use the ROUNDUP function.

Step 2)

Add the NEW Carrier rate matrix below into a ‘new’ spreadsheet in the file.

Make sure the Zone is referenceable.?

- Use the number ‘2’ as the column title instead of “Zone 2”.

You now have one Excel file with both your “Shipment History” and “NEW Carrier Rates” in separate spreadsheets.?

Step 3)

Here it is…

Use the following INDEX / MATCH formula to magically return a rate into the NEW Carrier Rate column.?

=INDEX(New_Rates!$A$4:$H$24, ← Define Rate Table (range)

MATCH(B4,New_Rates!$A$4:$A$24,0), ← Weight Reference (row)

MATCH(C4,New_Rates!$A$4:$H$4,0)) ← Zone Reference (column)

It’s important to use an absolute reference in the formula in order to easily copy it down and preserve reference to the rate matrix (eg. ‘$’ in front of the column and row to fix the cell location, such as $A$4).?

Want to see an example of the formula in action??

Click the link in the comment.

Step 4)?

Copy down the formula to all shipments.?

That’s it! ??

You have added the NEW Carrier Rate next to the Current.?

It only took you a couple minutes to complete.?

And you can do all sorts of comparisons of NEW Rate vs Current Rate.?

You now have a ?? fast way to add NEW rates for comparison.

Go give it a try. Link is in the comments.

If Excel formulas aren’t your thing, share this with a friend or a colleague who craves this stuff. You know who they are.?

Or, why not dive in and expand your knowledge and have some fun while doing it.?

THANK YOU SO MUCH for reading!

If this has helped you in any way, or you just enjoyed learning this tip, please leave a comment, like, or share it with your network on LinkedIn.?

I write about a variety of topics in E-commerce shipping and logistics ??. Let me know what else you’d like to learn about.?

I look forward to hearing from you.

Cheers!