The 3 Excel formulas that every 3PL should know

If Excel is not part of your daily workflow as a third party logistics warehouse, read on.

You are already using a best-in-class WMS software to manage your warehouse, store your data, and communicate with your clients.  That WMS system should be capable of extracting and reporting data to you in various ways.  So where does Excel fit in to the picture?

Excel is your tool for ad hoc reporting and data manipulation.  In the last 11 years of working only in the 3PL logistics industry, I have found the following three formulas come up time and again when asked to address a specific warehousing questions.

1.   IF( Something is TRUE,  then do THIS,  otherwise do THAT)

Let’s start off simple with a staple of programming:  the conditional statement.  This formula takes 3 parameters, a condition to check for, and then what the value should be if true or if false.

Example:

You have a spreadsheet with a list of detailed inventory.  (Side note: you should always start with a spreadsheet of the most detailed inventory.  Do you know how to get that quickly from your current WMS?).  You have a column for weight, with some products tracked in kilograms (KG) and other products are tracked in pounds (LB) and you want a quick total in pounds (LB).  Gross weight is in column F, weight standard is in column G, with the data starting on row 3. Let’s add column H with the following formula:

=IF( G3=”KG”, F3*2.2046 , F3 )

This statement says that if the unit of measure is KG, then we want to take the gross weight from column F and multiple by the conversion of KG to LB which is 2.2046.  If the value of the unit of measure is anything other than KG, we will simply use the value in column F.  This formula can then be copied from row 3 and pasted to all other rows, and then a sum can be calculated on the entire column H.

What to try next:  Try using =IFS to handle multiple conditionals if you have other weight standards than just KG and LB.

Extra credit: Do you have some pesky #N/A on your spreadsheet?  Try the =IFNA formula to convert the #N/A to blank or another value. =IFNA(A1,””)

2.  SUMIFS( Range to be summed, Range to be evaluated by conditional,  Conditional value, [additional conditionals] )

Now we are getting into the more advanced Excel formulas.  The SUMIFS statement allows you to take a detailed list and pick out individual rows that you want to SUM based on a condition.

Example:

As before, we are starting with our really detailed inventory list.  This time we want to find the SUM of certain units of measure.  How many drums are in the warehouse compared to bags, cases, or totes.  Because each client and item could track multiple units of measure, we really need to evaluate each line of inventory to get our answer.

If our detailed inventory has 9 rows (starting on row 3 through row 11) with the quantity in column D and the unit of measure in column E, we can create the following formula to get the total drums:

=SUMIFS(  D3:D11,  E3:E110,  “DRUM”  )

This statement will sum the values in column D, but only for rows where the value in column E is equal to DRUM.

What to try next:

COUNTIFS is the sister function to SUMIFS if you just want to count the number of rows rather can count the sum of a column.

Extra credit:

Create a list of all your units of measure and replace the word “DRUM” with a cell value on your sheet to create a quick list of all of your units of measures and the totals in the warehouse.  Make sure to lock your rows and columns using the $ sign.  For example, the “sum range” is unlikely to change, so you will want to use $A$1:$A$100 to tell excel to not change those values when you start copying and pasting.

3. INDEX( Range of values, Specific row, return this column value )
and MATCH( Value to find, in this range of cells, 0 = exact match  )

Now that we’ve graduated from Excel University, it is time to start on our Masters with my favorite pair of statements, the INDEX and MATCH.  These statements allow you to find matching data from two lists and pull data from one list to the other.  The MATCH formula performs a lookup on a table and returned the row number with a matching value.  The INDEX formula takes a row number and returns the value in specific column.

Example:

Our detailed inventory list doesn’t have some key pieces of data that are only stored in our item master file.  I have exported both to excel on separate sheets.  I want to take an Item Category value stored in the item master sheet and display it on each row of the detailed inventory sheet.  Sheet Item has 6 rows of items with the item code in column I and the category in column J.  Sheet Inventory has 13 rows with the item code in column B.  As before, with title rows the data starts on row 3.  We can then use the following formula to add the category to column F of the detail inventory sheet:

=INDEX(  I3:J8,  MATCH(  B3,  I3:I8,  0  ),  2  )

To understand this complex formula, let’s break out the two main components.  MATCH is used to find the row in the item sheet that matches the item code from inventory.  The first parameter is the item we are looking up in the inventory sheet and the second parameter is the list of items (the third parameter is always going to be 0 which tells Excel we are looking for an exact match).  The result of this MATCH formula may be a number like 4, which means we found the matching item code in row 4 of the item sheet.

Next, the INDEX command takes that row number, and looks up that row in the item sheet between rows 3 and 8.  The last parameter says that we want to get the value from the second column, which is the category code.  We have now merged data from an item master file into an inventory detail spreadsheet.  You will want to copy this formula to each row in the inventory detail file, but before doing so make sure to use the $ symbol to lock in the row and column values that you don’t want to change, such as:

=INDEX(  $I$3:$J$8,  MATCH(  B3,  $I$3:$I$8,  0  ),  2  )

What to try next:

INDEX and MATCH will return #N/A if no lookup is found.  This may be an expected result of your lookup, so go back to our first bullet above and use the IFNA formula clear out those pesky #N/A and replace it with a value of your choosing.

Extra Credit:

You often won’t have a single cell capable for lookup, and will need to MATCH values across multiple columns.  To do this try the CONCATENATE command to create a new column where you combine multiple values together to perform a MATCH.  For example, 3PLs will often have multiple clients using the same item code, so you can concatenate client and item information into a single cell to perform one INDEX and MATCH.