In this article we look at 5 Microsoft Excel functions that every logistics manager should know how to use.
If your anything like me then you are probably using excel on most days either reading reports or creating them to monitor or measure the key performance indicators of your logistics operations.
Over the years I have discovered a number very useful excel functions but the 5 listed below are the ones that I most regularly use.
- VLOOKUP
- TRIM
- CONCATENATE
- NETWORKDAYS
- WEEK BEGINNING DATE
A summary of what each function does and a practical example of how to apply each of the functions is described below.
To download a FREE copy of a spreadsheet that provides practical examples of each of the functions click on the link below.
VLOOKUP
VLOOKUP is by far the function that I use the most. The VLOOKUP function allows you to find things in one sheet and display them in another.
For example if you have an inventory list with the number of units on hand for each SKU (stock keeping unit) in one spreadsheet but the price for each SKU is stored in another sheet. Assuming the part number is exactly the same in both lists then you can use the VLOOKUP function to find the price for each SKU and insert it into the inventory list sheet. Or vice versa - add the units on hand to the price list.
The basic syntax is =VLOOKUP (cell content of item to find, range in which to find the item, column in range to return,true or false)
For more information - CLICK HERE
The VLOOKUP function is also utilized in number of the downloadable tools I have developed. It is useful for looking up variable currencies and shipping rates. Practical use cases can be found in my airfreight , LCL and FCL shipping cost calculators or my airfreight vs courier and LCL vs FCL cost comparison calculators.
The shipping cost calculators help to calculate the total cost of shipping via various modes.
The cost comparison calculators help to determine the most cost effective shipping mode to use.
CONCATENATE
The CONCATENATE function allows you join the data in one cell with the data in another.
For example, many SKUs will have variants of the base SKU e.g. size and color for apparel and footwear. This can be useful in the example above if the SKU format in the price list is different to the SKU format in the inventory list and you need to make them the same in order to perform the VLOOKUP function
The basic syntax is = CONCATENATE(content of cell 1, content of cell 2, content of cell 3)
Other option is to use the amersand & sign to get the same result =(content of cell 1 & content of cell 2 & content of cell 3)
The CONCATENATE function has been replaced by the CONCAT function in the lastest versions of excel
For more information - CLICK HERE
TRIM
Quite often when you receive or download data from service provider applications the data in each cell can include irregular spacing between elements or spaces at the the start or end of each cell. This is becomes problematic when using the VLOOKUP function (see above) as the function relies on the format of the data in each cell to be exactly the same in order to get a match.
The TRIM function removes all spaces from the data element except for single spaces between the data in the cell and ensures that you can find the data you are looking for.
The basic syntax is =TRIM(cell content)
For more information - CLICK HERE
NETWORKDAYS
The NETWORKDAYS function returns the number of business days between two dates, excluding weekends and, optionally, the holidays you specify. The start and finish dates must always be entered and holiday dates are optional.
As an example, this formula is great for calculating the number of work days (Monday to Friday) it takes from the time an order is received to the time it is delivered. Typically carriers do not deliver on the weekend and therefore the total transit time will be adjusted to take this into account i.e. an order dispatched on Friday and delivered on Monday will calculated as being delivered in one day as opposed to three.
The basic syntax is =NETWORKDAYS(start_date, end_date, [holidays])
For more information - CLICK HERE
WEEK BEGINNING DATE
Let me first point out that WEEK BEGINNING is not actually an Excel formula - its an adaption of another formula that I found in a post over at the MrExcel forum (see link below).
Using this function has become invaluable for my weekly reporting as it assigns a common data element to each line of the report which can then be used for filtering data in the spreadsheet and creating graphs or pivot tables based on a weeks activity.
To be honest, I don't understand the maths behind the formula but one thing I do know is that it works and that it has drasticly reduced the time it takes to prepare my weekly weekly reports!
The basic syntax is - =date - MOD(date 2,7) - date being the actual date of activity
For more information - CLICK HERE
Another one of my go to resources for all things excel is the Excel Addict - Francis Hayes sends out a weekly email "Spreadsheet Tips From An Excel Addict" and has massive archive of practical tips on various excel functions and for creating spreadsheets.
To download a FREE copy of a spreadsheet that provides practical examples of each of the functions click on the link below.
JOIN OUR COMMUNITY
Sign up to receive practical tips, tools and techniques for managing 3rd party logistics service providers delivered directly to your inbox.
Try it out. You can unsubscribe at any time and you can rest assured that we do not share our list with anyone – ever!
To join our community of 3rd party logistics service provider management professionals enter your details below
Leave a Reply