Spreadsheet Formulas, Functions, and Data Manipulation

Introduction and context

Supply-chain spreadsheets are often treated as simple calculation tools. In practice, they are small information systems: they store operational data, encode business rules, transform records, and support decisions about purchasing, inventory, transport, and customer service. A poorly designed formula can therefore create consequences well beyond a single cell. It may misstate available inventory, trigger an unnecessary purchase, conceal a late shipment, or produce a misleading performance report.

This chapter develops a disciplined approach to spreadsheet formulas, functions, and data manipulation. Examples use contemporary spreadsheet conventions such as Excel 365 or Google Sheets. Function names and dynamic-array behavior may vary slightly across platforms; users should verify local syntax and version support.

The central principle is that a formula should be treated as executable business logic. It must have a clear purpose, use appropriate data types, identify exceptional conditions, and be auditable by another person.

Learning objectives

By the end of this chapter, you should be able to:

1. Construct and audit relative, absolute, and mixed references.
2. Apply logical, aggregation, conditional, lookup, text, and date functions to supply-chain data.
3. Use dynamic-array functions to create flexible tracking and exception reports.
4. Manipulate text and dates without corrupting identifiers or time information.
5. Design formulas that manage missing, invalid, and exceptional data safely.
6. Evaluate spreadsheet solutions for accuracy, maintainability, governance, and managerial usefulness.

1. Formula architecture and reference discipline

A spreadsheet formula begins with = and may contain constants, cell references, operators, functions, and nested expressions. For example, if ordered quantity is in B2 and received quantity is in C2, the open quantity is:

In a worksheet, this may be entered as =B2-C2. The conceptual formula is more important than the syntax: the analyst must know what each field means and whether both quantities use the same unit.

Operators and precedence

Spreadsheet systems normally evaluate multiplication and division before addition and subtraction. Parentheses should be used whenever the intended order is not obvious. For example, remaining stock after allocation is =OnHand-(Reserved+Backordered), not =OnHand-Reserved+Backordered.

A useful inventory identity is:

where each term is measured in the same stockkeeping unit (SKU) and time basis. This expression assumes that on-order inventory is expected to arrive within the planning horizon and that reserved inventory is not already excluded from on-hand stock. If those assumptions are false, the formula can overstate availability.

Relative, absolute, and mixed references

A relative reference such as B2 changes when copied. An absolute reference such as $B$2 remains fixed. Mixed references fix either a column ($B2) or a row (B$2). Suppose a freight table stores a fuel surcharge rate in H1 and shipment cost in E2. The formula =E2*(1+$H$1) can be copied down while preserving the rate reference.

Reference discipline is especially important when formulas are copied across a product-location matrix. A formula that accidentally shifts a rate, currency conversion, or threshold can generate systematic errors that appear plausible.

Structured references and named ranges

Excel Tables permit references such as =[@Quantity]*[@UnitCost], which are usually more readable than cell coordinates. Named ranges such as ReorderPoint or TaxRate can clarify business meaning. Names should be governed: duplicate names, hidden names, and names with unclear definitions reduce auditability.

2. Logical and conditional reasoning

Logical functions convert business rules into explicit decisions. The most common functions are IF, IFS, AND, OR, and NOT.

An IF function has the form =IF(condition, value_if_true, value_if_false). For a shipment whose actual delivery date is in D2 and promised date is in C2, a simple status rule is:

=IF(D2="","In transit",IF(D2<=C2,"On time","Late"))

This formula assumes a blank actual date means that the shipment has not yet been confirmed delivered. It should not be used if blanks also represent missing data or data-entry failure; in that case, a separate data-quality status is required.

AND requires every condition to be true, whereas OR requires at least one condition to be true. For example:

=IF(AND([@OnHand]<[@ReorderPoint],[@OnOrder]=0),"Expedite review","No immediate action")

The formula identifies a potential shortage only when inventory is below the reorder point and no replenishment is currently recorded.

IFS is useful for mutually exclusive bands, such as service performance:

=IFS([@OTIF]>=0.95,"Excellent",[@OTIF]>=0.90,"Acceptable",TRUE,"Requires action")

Because conditions are evaluated in sequence, the most restrictive threshold should appear first. Percentages must be stored consistently as decimals or percentage-formatted values.

Choosing between nested logic and lookup tables

Nested IF formulas become difficult to inspect when rules change. A rate table or threshold table is generally preferable when business rules are maintained by nontechnical users. For example, a carrier surcharge can be assigned by zone using an approximate lookup table, with explicitly documented breakpoints. This separates policy from calculation and improves change control.

3. Aggregation and conditional aggregation

Aggregation functions summarize operational records. SUM, AVERAGE, MIN, MAX, COUNT, and COUNTA are basic tools, but analysts must understand what each counts and what it excludes.

If daily demand observations are 120, 80, 100, and 60 units, then:

The average is appropriate only if all observations are comparable and no relevant periods have been omitted. A zero-demand day may be a genuine zero or a missing transaction; the analyst must distinguish the two.

SUMIF and SUMIFS aggregate values subject to criteria. For a transaction table, monthly demand for SKU A at warehouse W1 might be calculated as:

=SUMIFS(Transactions[Quantity],Transactions[SKU],"A",Transactions[Warehouse],"W1",Transactions[Month],"2025-03")

COUNTIF and COUNTIFS count records meeting conditions, while AVERAGEIF and AVERAGEIFS calculate conditional means. Criteria containing operators require quotation marks, for example ">0" or "<=5".

Weighted averages

A simple average is inappropriate when observations represent different quantities or exposures. A weighted average unit cost is:

where qᵢ is the quantity purchased in transaction i, and cᵢ is its unit cost. For 100 units at $10 and 20 units at $13:

The result assumes all costs are comparable and excludes costs that should be treated separately, such as expedited freight or duties.

SUMPRODUCT can calculate weighted totals without helper columns. For quantities in B2:B10 and unit costs in C2:C10, =SUMPRODUCT(B2:B10,C2:C10) returns total purchase value, provided the ranges have equal dimensions and compatible units.

4. Lookup functions and relational thinking

Supply-chain spreadsheets frequently contain related tables: an order table may contain a SKU, while a product master contains description, category, supplier, and standard lead time. A lookup retrieves an attribute using a key.

XLOOKUP

XLOOKUP is generally preferred in modern Excel because it can search in either direction and allows explicit not-found behavior:

=XLOOKUP([@SKU],Products[SKU],Products[LeadTimeDays],"SKU not found")

The lookup key should be unique or the analyst must define which duplicate record is authoritative. A successful lookup does not prove that the returned data is correct; it only proves that a matching key was found.

INDEX and MATCH

The combination INDEX and MATCH remains useful for compatibility and flexible models:

=INDEX(Products[LeadTimeDays],MATCH([@SKU],Products[SKU],0))

The 0 requests an exact match. Approximate matching is appropriate only when the lookup range is correctly sorted and the business rule genuinely uses intervals, such as freight rates by weight band.

VLOOKUP and common risks

VLOOKUP searches the first column of a table and returns a value to its right. Its limitations include dependence on column position and frequent accidental use of approximate matching. If approximate matching is intended, the first column must be sorted in ascending order; otherwise results may be incorrect without an obvious error.

Key construction

When no single field uniquely identifies a record, a composite key may be created, such as Warehouse|SKU|Week. Use a delimiter that cannot occur in the component values, and normalize inputs before concatenation. For example:

=UPPER(TRIM([@Warehouse]))&"|"&UPPER(TRIM([@SKU]))&"|"&TEXT([@Week],"yyyy-mm-dd")

Composite keys should be documented and checked for collisions.

5. Text manipulation and data normalization

Text functions are essential because operational data often arrives from emails, enterprise systems, carrier portals, and manually maintained files. Common functions include TRIM, CLEAN, UPPER, LOWER, PROPER, LEFT, RIGHT, MID, LEN, FIND, SEARCH, SUBSTITUTE, TEXTBEFORE, TEXTAFTER, and TEXTJOIN.

TRIM removes surplus spaces, while CLEAN removes many nonprinting characters. They do not correct every Unicode or encoding problem. A visible SKU such as AB-001 may still differ from AB–001 if one uses a different dash character.

Example normalization:

=UPPER(TRIM(SUBSTITUTE(A2," ","")))

This removes spaces and converts letters to uppercase. It should not be applied blindly to names or descriptions where spaces are meaningful.

If a purchase-order reference has the form PO-2025-00481, TEXTAFTER and TEXTBEFORE can isolate components. Older spreadsheet versions may require LEFT, RIGHT, MID, and FIND. Text functions return text, not necessarily numeric values. A number extracted from text may need conversion using VALUE, although leading zeros in identifiers must be preserved as text.

A robust data model separates identifiers from descriptions. Converting an SKU to a number can destroy leading zeros and create mismatches in lookups.

6. Dates, times, and service measurement

Spreadsheets commonly store dates as serial numbers: the displayed date is a formatted representation of an underlying numeric value. This enables subtraction, but it creates risks when dates are imported as text or when time zones are ignored.

For order date in B2 and delivery date in C2, elapsed calendar days are:

=C2-B2

NETWORKDAYS counts working days between dates, optionally excluding a holiday range:

=NETWORKDAYS(B2,C2,Holidays[Date])

The function’s default weekend convention is Saturday and Sunday. NETWORKDAYS.INTL should be used when the operating calendar differs, such as a Friday–Saturday weekend.

WORKDAY calculates a future working date, useful for planned receipt dates:

=WORKDAY([@OrderDate],[@LeadTimeDays],Holidays[Date])

These functions assume dates are valid serial dates and that the holiday list is complete. They do not automatically account for local cut-off times, partial working days, daylight-saving transitions, or carrier-specific calendars.

Date-based service indicators

A delivery is on time when the actual delivery date is no later than the promised date. At aggregate level, on-time delivery rate is:

The denominator must be defined. Cancelled orders, customer-requested delays, and shipments without confirmed delivery may require separate treatment. A spreadsheet should therefore include an eligibility field rather than silently excluding records.

7. Dynamic arrays and modern spreadsheet design

Dynamic-array functions return multiple results that spill into adjacent cells. Important functions include FILTER, SORT, UNIQUE, SEQUENCE, and SORTBY.

To display all open orders for warehouse W1:

=FILTER(Orders, (Orders[Warehouse]="W1")*(Orders[Status]="Open"), "No open orders")

In spreadsheet logic, multiplication between Boolean arrays acts like AND, while addition can represent OR when results are handled carefully. The spill range changes automatically as the source table changes, reducing manual copying.

To create a sorted supplier list:

=SORT(UNIQUE(Orders[Supplier]))

Dynamic arrays improve responsiveness but introduce operational risks. A blocked spill range produces an error; users may overwrite a spilled result; and downstream formulas may not be designed for changing row counts. A dashboard should identify spill areas and protect them where appropriate.

LET can improve readability and efficiency by naming intermediate expressions:

=LET(open,FILTER(Orders,Orders[Status]="Open"),SORT(open,5,1))

The exact sort-column number depends on the returned array. Named variables should describe business meaning, not merely cell locations.

8. Error management and data quality

Errors are information. Suppressing every error with IFERROR can hide broken keys, invalid dates, or incomplete integrations. Common errors include #N/A for a missing lookup, #VALUE! for incompatible types, #DIV/0! for an invalid denominator, #REF! for a broken reference, and #SPILL! for a blocked dynamic result.

Use IFNA when only a missing lookup should receive a special response:

=IFNA(XLOOKUP([@SKU],Products[SKU],Products[Cost]),"Missing master data")

Use IFERROR when multiple predictable errors should receive a common treatment, but document what is being concealed:

=IFERROR([@ActualCost]/[@Quantity],"")

A blank output is not always safe. It may be interpreted as zero or as complete data. In operational reports, explicit labels such as Missing quantity or Invalid date often support better control.

A useful validation architecture separates three states:

1. Valid operational result.
2. Valid exception requiring action.
3. Invalid or incomplete data requiring correction.

For example, a shipment can be legitimately late, but a shipment with no promised date is not necessarily late; it is unassessable. Conflating these states damages performance measurement.

9. A worked application: inventory exception tracker

Consider an illustrative distribution company managing replenishment for multiple warehouses. Its Inventory table contains SKU, Warehouse, OnHand, Reserved, OnOrder, ReorderPoint, Supplier, and LeadTimeDays. Its Products table contains SKU, Description, UnitCost, and CasePack. Management wants an exception tracker that identifies shortages, estimates exposure, and retrieves supplier information.

Step 1: calculate available inventory

=[@OnHand]+[@OnOrder]-[@Reserved]

This is a planning measure, not a physical count. If on-order quantities have uncertain receipt dates, a second measure should distinguish confirmed from unconfirmed supply.

Step 2: classify replenishment status

=IFS([@Available]<0,"Allocation deficit",[@Available]<[@ReorderPoint],"Below reorder point",TRUE,"Within policy")

The negative condition is checked first because an allocation deficit is more urgent than a routine reorder-point exception.

Step 3: calculate estimated inventory value

=[@Available]*XLOOKUP([@SKU],Products[SKU],Products[UnitCost],0)

The result assumes that available quantity and unit cost use compatible units. If quantity is in cases and cost is per each, the formula must include the case-pack conversion:

Units cancel correctly, producing dollars.

Step 4: retrieve supplier and validate the key

=IFNA(XLOOKUP([@SKU],Products[SKU],Products[Supplier]),"Missing supplier")

A missing supplier is a master-data exception, not evidence that the item has no supplier.

Step 5: produce a management view

=SORT(FILTER(Inventory,(Inventory[Status]<>"Within policy")*(Inventory[Warehouse]=$B$1),"No exceptions"),5,-1)

The expression filters exceptions for the warehouse selected in B1 and sorts the result by the fifth returned column in descending order. In production, a named field or helper column is safer than relying on a positional column number.

Interpretation

Suppose SKU X has 40 units on hand, 15 reserved, 0 on order, and a reorder point of 30. Available inventory is 25 units, so the item is below the reorder point. If unit cost is $18, estimated available value is $450. The formula does not prove that a purchase of 5 units is optimal. Order quantity may depend on case packs, minimum order quantities, forecast demand, safety stock, supplier lead time, and budget constraints. Spreadsheet output supports a decision; it does not replace policy or professional judgment.

10. Case study: reducing false late-shipment alerts

The following is an illustrative case. Northstar Components used a workbook combining carrier data, customer promises, and order records. Its late report showed a 14% late-delivery rate, but operations managers believed the figure was inflated.

Background and problem

The workbook used =IF(ActualDate>PromisedDate,"Late","On time"). Blank actual dates were treated inconsistently: some rows were blank, while others contained text such as Pending. Customer-requested date changes were not recorded separately. In addition, imported dates from one carrier were text strings in day-month-year format.

Analysis

The team profiled the fields using counts, unique-value lists, and validation checks. It found four distinct conditions: delivered on time, delivered late, in transit, and unassessable because the promise date was invalid or absent. It also found that 8% of records had customer-requested changes.

Approach and solution

The team created explicit columns for OriginalPromiseDate, CurrentPromiseDate, ActualDate, DeliveryStatus, Eligibility, and DataQuality. A controlled formula was introduced:

=IFS(NOT([@Eligibility]),"Not eligible",[@ActualDate]="","In transit",[@ActualDate]<=[@CurrentPromiseDate],"On time",TRUE,"Late")

Eligibility was determined by documented policy rather than by whether a cell happened to be nonblank. Imported dates were converted through a controlled transformation and checked against plausible date ranges. A separate exception report used FILTER to display invalid records rather than hide them.

Outcome

The reported late rate fell, but the more important outcome was improved interpretability. Managers could distinguish genuine service failures from pending deliveries, customer-requested changes, and data defects. The organization also established a monthly review of lookup failures, duplicate keys, and formula changes.

Learning points

First, a metric is only as reliable as its denominator and classification rules. Second, error management should expose uncertainty rather than erase it. Third, separating data preparation, business logic, and presentation makes a workbook easier to test and govern.

11. Governance, ethics, and contemporary practice

Spreadsheet errors are a governance issue because decisions may affect suppliers, employees, customers, and financial results. Good practice includes:

• maintaining a single controlled source for master data;
• documenting field definitions, units, calendars, and assumptions;
• protecting formula cells while leaving input cells clearly marked;
• using version history and change logs;
• testing formulas with normal, boundary, missing, and invalid cases;
• reconciling totals to an independent source;
• avoiding hidden sheets and undocumented hard-coded constants;
• separating personally identifiable information from operational reports;
• applying least-privilege access and appropriate retention controls.

AI-assisted spreadsheet features can generate formulas, summarize tables, or detect patterns. They can improve productivity but may misunderstand field semantics, invent criteria, or produce formulas that silently mishandle blanks. A human reviewer should test generated formulas against known cases, inspect references, verify units, and approve any change affecting operational decisions. AI should assist with formulation and exploration, not replace accountability.

For graduate professionals, the key design question is not simply whether a formula works today. It is whether the logic remains correct when data volume, suppliers, calendars, policies, and users change.

Best-practice checklist

Before releasing a tracking workbook, ask:

1. Are keys unique, normalized, and appropriately typed?
2. Are dates genuine dates and are time-zone assumptions documented?
3. Are formulas copied with correct relative and absolute references?
4. Are units consistent, including each, case, pallet, kilogram, and currency?
5. Are missing, invalid, pending, and legitimate exception states distinct?
6. Are lookup failures visible and actionable?
7. Are dynamic spill areas protected and documented?
8. Can an independent reviewer reproduce the result?
9. Have boundary cases and changed policies been tested?
10. Is the workbook the appropriate tool, or should the process move to a governed database or planning system?

Chapter Summary

Spreadsheet formulas are executable representations of supply-chain rules. Reliable models begin with clear data types, units, keys, calendars, and assumptions. Relative, absolute, and mixed references control how formulas behave when copied. Logical functions encode classifications and decisions; aggregation and conditional aggregation summarize demand, cost, and service; lookup functions connect transactional records to master data; text functions normalize imported values; and date functions support lead-time and service calculations.

Dynamic arrays enable flexible exception reports, but spill behavior must be governed. Error functions should distinguish missing data from legitimate operational exceptions rather than conceal problems. Weighted averages and unit conversions require particular care because simple arithmetic can be misleading when quantities or units differ. Finally, spreadsheet quality depends on testing, documentation, access control, versioning, and human accountability. In supply-chain management, a spreadsheet is not merely a grid of numbers: it is a controlled analytical artifact that influences operational decisions.

Key Terms

Relative reference: A cell reference that changes when a formula is copied.

Absolute reference: A reference whose row and column remain fixed, such as $B$2.

Mixed reference: A reference with either its row or column fixed.

Logical function: A function that evaluates conditions and returns decisions or classifications.

Conditional aggregation: Summarizing values only when specified criteria are met.

Weighted average: An average in which observations contribute according to their quantity or importance.

Lookup: Retrieval of an attribute from another table using a key.

Exact match: A lookup requiring the key to equal a stored value.

Approximate match: A lookup based on an interval or nearest qualifying breakpoint.

Composite key: A key formed by combining two or more fields.

Normalization: Preparing data so equivalent values use a consistent representation.

Dynamic array: A formula result that automatically spills into multiple cells.

Spill range: The cells occupied by a dynamic-array result.

Data type: The semantic kind of a value, such as text, number, date, or Boolean.

Error management: The deliberate detection, classification, and handling of formula or data errors.

Eligibility: A documented determination that a record may be included in a metric.

Master data: Relatively stable reference data such as products, suppliers, locations, and calendars.

Auditability: The ability to inspect, reproduce, and explain a result.

Review Questions

Short Answer Questions

1. What is the difference between a relative reference and an absolute reference?
2. Why can a simple average be misleading for unit-cost analysis?
3. When is IFNA preferable to IFERROR?
4. What conditions must hold for an approximate lookup to be reliable?
5. Why should missing delivery dates not automatically be classified as late?

Long Answer Questions

1. Explain how logical, conditional aggregation, lookup, and date functions can be combined to create a supplier-performance tracker.
2. Discuss the risks of using hard-coded thresholds in nested IF formulas and compare them with a policy lookup table.
3. Explain how text normalization affects SKU matching, and propose controls for preventing identifier corruption.
4. Evaluate the benefits and governance risks of dynamic-array formulas in a shared operational workbook.
5. Develop a testing and documentation plan for a spreadsheet used to calculate inventory exceptions.

Analytical / Application Questions

1. An item has 240 units on hand, 80 reserved, 100 on order, and a reorder point of 300. Calculate available inventory and determine whether it is below the reorder point. State an assumption about the timing of on-order stock.
2. A workbook reports 12 late deliveries out of 100 records. Ten records have no actual delivery date, and five were delayed at the customer’s request. Design an eligibility rule and explain how the service rate denominator should be constructed.
3. Design formulas for a dynamic report that lists all open orders for a selected warehouse, retrieves supplier lead time from a product master, flags missing SKUs, and sorts orders by promised date.

Multiple Choice Questions with Answers

Question 1

Which reference remains fixed in both row and column when copied?

A. A1

B. $A$1

C. A$1

D. $A1

Correct Answer: B. $A$1

Question 2

A company buys 100 units at $10 and 20 units at $13. What is the weighted average unit cost?

A. $10.00

B. $10.30

C. $10.50

D. $11.50

Correct Answer: C. $10.50

Question 3

Which function is generally most appropriate for an exact modern lookup with a custom not-found message?

A. XLOOKUP

B. AVERAGEIF

C. NETWORKDAYS

D. TEXTJOIN

Correct Answer: A. XLOOKUP

Question 4

What is the principal risk of treating every blank actual delivery date as a late delivery?

A. It increases lookup speed

B. It confuses pending or missing data with a service failure

C. It converts dates into numbers

D. It prevents conditional formatting

Correct Answer: B. It confuses pending or missing data with a service failure

Question 5

Which function counts working days between two dates while allowing a holiday range?

A. WORKDAY

B. DATEDIF

C. NETWORKDAYS

D. DATEVALUE

Correct Answer: C. NETWORKDAYS

Question 6

What does a dynamic-array formula do when its result contains multiple values?

A. It automatically spills into adjacent cells

B. It converts all values to text

C. It deletes the source table

D. It forces an approximate lookup

Correct Answer: A. It automatically spills into adjacent cells

Question 7

Which formula best represents available inventory when on-order stock is assumed to arrive within the planning horizon?

A. On-hand + reserved − on-order

B. On-hand − reserved + on-order

C. On-hand + reserved + on-order

D. Reserved − on-hand − on-order

Correct Answer: B. On-hand − reserved + on-order

Question 8

Why is IFNA often preferable to IFERROR for a missing product lookup?

A. It handles only missing-lookup errors and does not mask unrelated errors

B. It converts text to dates

C. It sorts the lookup range

D. It guarantees that the key is unique

Correct Answer: A. It handles only missing-lookup errors and does not mask unrelated errors

Question 9

Which function is most suitable for calculating a total from quantities multiplied by corresponding unit costs?

A. SUMPRODUCT

B. UNIQUE

C. LEFT

D. WORKDAY

Correct Answer: A. SUMPRODUCT

Question 10

A SKU is stored as 00125. Why should it generally remain text rather than be converted to a number?

A. Numbers cannot be used in formulas

B. Text is always faster than numbers

C. Conversion can remove leading zeros and break identifier matching

D. Text prevents all lookup errors

Correct Answer: C. Conversion can remove leading zeros and break identifier matching