ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
MS Excel Formulas for Logistics & Supply Chain Management
2
CategoryFormulaUse CaseDescription
3
Basic Calculations=A1+B1Total cost (fuel + labor)Adds two values for cost summation.
4
=A1-B1Cost savings or differencesSubtracts one value from another.
5
=A1*B1Total cost (unit price * quantity)Multiplies values to get total cost.
6
=A1/B1Cost per unitDivides total cost by quantity.
7
SUM & AVERAGE=SUM(A1:A10)Total transportation costsSums up values in a range.
8
=AVERAGE(A1:A10)Average cost per shipmentCalculates the mean of a range.
9
=SUMIF(A1:A10, ">500", B1:B10)
Sum values where shipments exceed 500 kg
Adds values based on a condition.
10
=SUMIFS(A1:A10, B1:B10, ">100", C1:C10, "<500")Sum values based on multiple conditionsAdds values if multiple conditions are met.
11
=AVERAGEIF(A1:A10, ">100")Average shipment size above 100 unitsAverages values that meet a condition.
12
IF & LOGICAL FUNCTIONS=IF(A1>100, "High", "Low")Classify shipments as high/low volumeReturns "High" if shipment >100.
13
=IF(AND(A1>100, B1<50), "Priority", "Normal")Prioritize shipmentsUses multiple conditions for prioritization.
14
=IF(OR(A1>500, B1>1000), "Bulk Order", "Regular")Identify bulk ordersReturns "Bulk Order" if either condition is met.
15
LOOKUP & REFERENCE=VLOOKUP(1001, A2:C10, 2, FALSE)Find product name using IDSearches for a value in a table vertically.
16
=HLOOKUP("Cost", A1:D4, 2, FALSE)Lookup cost from a tableSearches for a value in a table horizontally.
17
=INDEX(A2:A10, MATCH(1001, B2:B10, 0))Get data dynamicallyRetrieves a value based on position.
18
=CHOOSE(2, "Air", "Sea", "Land")Select 2nd transport modePicks a value from a list.
19
TEXT FUNCTIONS=LEFT(A1, 3)Extract first 3 characters (SKU prefix)Extracts first few characters from text.
20
=RIGHT(A1, 4)Extract last 4 characters (batch number)Extracts last few characters from text.
21
=MID(A1, 2, 3)Extract middle part of a codeExtracts a portion of text from a string.
22
=TRIM(A1)Remove unwanted spacesCleans up extra spaces from text.
23
=CONCATENATE(A1, "-", B1)Merge order ID & warehouse IDJoins two or more text strings together.
24
=TEXT(A1, "YYYY-MM-DD")Convert dates into standard formatFormats numbers and dates as text.
25
DATE & TIME FUNCTIONS=TODAY()Get current dateReturns today's date.
26
=NOW()Get current date & timeReturns the current date and time.
27
=EDATE(A1, 3)Add 3 months to lead timeShifts a date by a specified number of months.
28
=EOMONTH(A1, 0)Find last day of the monthReturns the last day of the month for a given date.
29
=NETWORKDAYS(A1, B1)Working days between dates
Calculates the number of working days between two dates.
30
=WORKDAY(A1, 10)Find delivery date after 10 working daysReturns a date after a given number of workdays.
31
=HOUR(A1)Extract hour from timestampReturns the hour portion from a time value.
32
=MINUTE(A1)Extract minute from timestampReturns the minute portion from a time value.
33
CONDITIONAL FORMATTING=A1>1000Highlight shipments above 1000 unitsUsed in conditional formatting for highlighting values.
34
=B1="Delayed"Highlight delayed shipmentsFormats cells based on shipment status.
35
ERROR HANDLING=IFERROR(A1/B1, "Check Data")Avoid division errorsPrevents errors when dividing by zero.
36
=IFNA(VLOOKUP(1001, A2:B10, 2, FALSE), "Not Found")Handle missing dataAvoids errors if a value is missing.
37
RANK & SORT=RANK(A1, A1:A10)Rank shipments by volumeAssigns a rank based on numerical order.
38
=LARGE(A1:A10, 1)Find highest shipment volumeReturns the nth largest value in a dataset.
39
=SMALL(A1:A10, 1)Find smallest shipment volumeReturns the nth smallest value in a dataset.
40
ROUNDING & FORMATTING=ROUND(A1, 2)Round to 2 decimal places
Rounds a number to a specified number of decimal places.
41
=CEILING(A1, 10)Round up to nearest 10Rounds a number up to the nearest multiple.
42
=FLOOR(A1, 10)Round down to nearest 10Rounds a number down to the nearest multiple.
43
ORDER PROCESSING=COUNTIF(A1:A100, "Delivered")Count delivered ordersCounts the number of times a condition is met.
44
=COUNTIFS(A1:A100, "Shipped", B1:B100, ">2025-03-01")Count orders shipped after a dateCounts based on multiple criteria.
45
WAREHOUSE CAPACITY UTILIZATION
=A1/B1*100Calculate warehouse space utilization %Calculates percentage of warehouse space used.
46
COST COMPARISON=IF(A1<B1, "Cheaper", "Expensive")Compare supplier pricesCompares two values and returns a label.
47
SERVICE LEVEL CALCULATION=COUNTIF(A1:A100, "On-Time")/COUNT(A1:A100)% of on-time deliveriesCalculates the proportion of on-time shipments.
48
Logistics Guide | The SCM Academy | www.logisticsstudy.com | Logistics Mastery Weekly | The Supply Chain Insider | LogiTrends
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100