Formulas, Functions and Spreadsheet Logic

Introduction

Accounting spreadsheets are not merely electronic ledgers. They are computational models in which cell references, operators, functions, assumptions, and controls work together to transform source data into financial information. A well-designed spreadsheet can calculate depreciation, classify transactions, prepare budgets, test variances, reconcile accounts, and support management decisions. A poorly designed spreadsheet can produce materially misstated information while appearing plausible.

This chapter develops the logic required to construct, interpret, audit, and improve Excel formulas. It focuses on reference types, arithmetic and logical operators, conditional aggregation, lookup and text functions, dates, financial calculations, dynamic arrays, and error diagnosis. The examples use standard Excel syntax. Function availability may vary by Excel version; dynamic-array functions such as FILTER, SORT, UNIQUE, and XLOOKUP are most reliable in Microsoft 365 and recent perpetual versions.

Learning Objectives

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

• distinguish relative, absolute, and mixed cell references;
• apply arithmetic, comparison, and logical operators correctly;
• construct formulas using IF, SUMIFS, COUNTIFS, and lookup functions;
• manipulate text and dates for accounting analysis;
• use common financial functions and interpret their assumptions;
• apply dynamic-array functions while controlling spill behavior;
• diagnose common formula errors and design effective controls; and
• evaluate spreadsheet solutions from professional, ethical, and governance perspectives.

1. The Spreadsheet Formula as a Computational Model

A formula begins with an equals sign (=). It may contain constants, cell references, operators, functions, and parentheses. For example:

=B5C5(1-D5)

If B5 is quantity, C5 is unit price, and D5 is a discount rate, the formula calculates net sales before tax. The model is transparent only if the labels, units, assumptions, and references are clear.

A formula should be understood as a relationship between inputs and an output. In accounting, an input may be a transaction amount, date, tax rate, useful life, budget, or exchange rate. Outputs may include gross profit, receivables ageing, inventory value, or a variance.

Order of Operations

Excel generally evaluates formulas in the following order:

1. references and ranges;
2. negation, such as -A1;
3. percentages and exponentiation;
4. multiplication and division;
5. addition and subtraction; and
6. comparisons such as =, >, <, >=, <=, and <>.

Parentheses should be used whenever they improve meaning or remove ambiguity. For example, =Revenue-Costs/Revenue is not the same as =(Revenue-Costs)/Revenue. The first subtracts the cost-to-revenue ratio from revenue; the second calculates the gross margin ratio when Revenue and Costs are monetary amounts.

2. Cell References and Model Copying

Relative References

A relative reference changes when a formula is copied. If E5 contains =C5D5, copying it to E6 changes the formula to =C6D6. Relative references are useful when the same calculation applies to multiple rows.

Absolute References

An absolute reference remains fixed when copied. The notation $B$2 fixes both the column and row. If B2 contains a tax rate, the formula =E5*(1-$B$2) can be copied down without changing the tax-rate reference.

Mixed References

A mixed reference fixes either the column or the row:

• $B5 fixes column B but permits the row to change;
• B$5 fixes row 5 but permits the column to change.

Mixed references are particularly useful in two-dimensional schedules. For example, if a table contains products in rows and months in columns, a formula may use $A6 to fix the product column and B$5 to fix the month row.

Pressing F4 while editing a reference usually cycles through these forms. Named ranges, such as TaxRate, can provide an alternative to absolute references and often improve readability.

Structured References

Excel Tables provide structured references. If a table is named Sales, the formula =SUMIFS(Sales[Amount],Sales[Region],H2) is usually easier to interpret than a formula based on coordinates. Structured references also expand automatically when records are added.

3. Operators and Logical Expressions

Arithmetic operators include +, -, *, /, and ^. The percent sign expresses a percentage, so 15% is equivalent to 0.15. Comparison operators return TRUE or FALSE:

• = equal to;
• <> not equal to;
• > greater than;
• < less than;
• >= greater than or equal to; and
• <= less than or equal to.

Logical functions combine conditions. AND returns TRUE only when every condition is TRUE. OR returns TRUE when at least one condition is TRUE. NOT reverses a logical result.

For example:

=AND(B2="Approved",C2>0)

returns TRUE only when the status is Approved and the amount is positive. Text criteria normally require quotation marks. A criterion that combines an operator with a cell reference uses concatenation, such as ">="&H2.

4. Conditional Logic with IF

The basic syntax is:

=IF(logical_test,value_if_true,value_if_false)

For example:

=IF(E5>0,"Profit","Loss or break-even")

A nested IF can classify ranges, but excessive nesting is difficult to audit. The IFS function or a lookup table may be preferable for many categories:

=IFS(E5>=0.20,"High",E5>=0.10,"Medium",TRUE,"Low")

The conditions should be arranged carefully. Since Excel returns the result associated with the first TRUE condition, overlapping conditions can create unintended classifications.

A common accounting application is materiality screening:

=IF(ABS(Variance)>=Materiality,"Investigate","No immediate investigation")

Here, ABS ensures that both positive and negative variances are assessed by magnitude.

Blank cells require attention. =IF(A1="","Missing",A1*B1) treats an empty string as missing, but a cell containing a formula returning "" also behaves as visually blank. ISBLANK tests whether a cell is genuinely empty and may produce a different result.

5. Conditional Aggregation

SUMIF and SUMIFS

SUMIF applies one condition:

=SUMIF(RegionRange,"East",AmountRange)

SUMIFS applies multiple conditions:

=SUMIFS(AmountRange,RegionRange,"East",DateRange,">="&StartDate,DateRange,"<="&EndDate)

The sum range and each criteria range should have matching dimensions. Criteria such as "East", ">1000", and "<>Cancelled" are interpreted as conditions rather than ordinary text.

Suppose a sales table contains Region, Product, Date, Status, and Amount. To sum completed sales for the East region during January, use:

=SUMIFS(Sales[Amount],Sales[Region],"East",Sales[Status],"Completed",Sales[Date],">="&DATE(2025,1,1),Sales[Date],"<"&DATE(2025,2,1))

Using a less-than next-month boundary is often safer than using the last day of the month, especially when date-time values contain times.

COUNTIF and COUNTIFS

COUNTIF counts records matching one criterion; COUNTIFS counts records matching several criteria:

=COUNTIFS(Sales[Region],"East",Sales[Status],"Completed")

A COUNTIFS result is a count of rows, not a sum of amounts. Confusing the two can materially distort operational and financial reporting.

SUMPRODUCT

SUMPRODUCT multiplies corresponding elements and sums the products. For quantities qᵢ and unit prices pᵢ, total value is:

In Excel, this may be written as =SUMPRODUCT(QuantityRange,PriceRange). It can also perform conditional calculations, but SUMIFS is usually clearer when the task is straightforward aggregation.

6. Lookup Functions

Lookup functions retrieve a related value from a reference table. A lookup table should have a unique key whenever a single result is expected. Duplicate keys create ambiguity.

XLOOKUP

The modern form is:

=XLOOKUP(LookupValue,LookupArray,ReturnArray,"Not found")

For example:

=XLOOKUP(A2,Customers[CustomerID],Customers[CreditLimit],"Missing customer")

XLOOKUP can search in either direction and can return an explicit not-found message. It is generally more flexible than the older VLOOKUP function.

VLOOKUP and HLOOKUP

VLOOKUP searches the first column of a table and returns a value from a column to its right:

=VLOOKUP(A2,$J$2:$M$100,4,FALSE)

The fourth argument should normally be FALSE for an exact match. TRUE requests an approximate match and requires the first column to be sorted in ascending order. Omitting this argument is a frequent source of silent errors.

INDEX and MATCH

The combination =INDEX(ReturnRange,MATCH(Key,KeyRange,0)) separates the lookup and return ranges and works in older Excel versions. MATCH with 0 requests an exact match.

Approximate Matching

Approximate lookups are appropriate for tax bands, commission tiers, or credit classifications when thresholds are sorted. A threshold table should define whether boundaries are inclusive. For example, a rate applying at income ≥ 50,000 requires a clearly ordered threshold design.

7. Text Functions in Accounting Work

Text functions are valuable for cleaning imported data, constructing identifiers, and extracting information from descriptions.

• LEFT(text,n) returns the first n characters.
• RIGHT(text,n) returns the last n characters.
• MID(text,start,n) extracts characters from a specified position.
• LEN(text) counts characters.
• TRIM(text) removes extra ordinary spaces.
• UPPER, LOWER, and PROPER change letter case.
• TEXTJOIN(delimiter,ignore_empty,text1,...) combines text.
• CONCAT joins values.
• SUBSTITUTE(text,old,new) replaces specified text.
• VALUE(text) converts numeric text to a number when the format is interpretable.

For example, an invoice code such as UK-2025-00418 can be separated with TEXTBEFORE and TEXTAFTER in current Excel versions. Cleaning should not destroy meaningful leading zeros in account or customer codes. A code such as 00127 should often remain text rather than being converted to the number 127.

The ampersand concatenation operator can build a label:

=A2&" - "&B2

When displaying numbers inside text, use TEXT to control formatting:

="Margin: "&TEXT(C2,"0.0%")

The resulting value is text and should not be used in later numerical calculations without appropriate conversion.

8. Dates and Times

Excel stores ordinary dates as serial numbers, which permits subtraction and comparison. A date difference can therefore be calculated as:

=EndDate-StartDate

The result is measured in days, assuming both values are valid dates. Important functions include:

• DATE(year,month,day) constructs a date;
• YEAR, MONTH, and DAY extract components;
• TODAY() returns the current date;
• EOMONTH(start_date,months) returns the month-end date after a specified number of months;
• EDATE(start_date,months) shifts a date by whole months;
• NETWORKDAYS(start_date,end_date,holidays) counts working days; and
• YEARFRAC(start_date,end_date,basis) estimates the fraction of a year under a selected day-count basis.

For an ageing calculation, assuming invoice date is in B2 and the reporting date is in H1:

=MAX(0,$H$1-B2)

This returns non-negative elapsed days when H1 is not earlier than B2. If date-time values are used, time components may affect results. A consistent date convention and documented day-count basis are essential in interest and finance calculations.

9. Financial Functions

Excel financial functions implement standard time-value-of-money relationships. Their output depends on timing conventions, sign conventions, and the assumption that the interest rate is appropriate for the period.

Present and Future Value

For a single future cash flow, present value is:

where PV is present value, FV is future value, r is the effective rate per period, and n is the number of periods. The formula assumes compounding once per period and a constant rate.

For equal end-of-period payments C, the present value of an ordinary annuity is:

when r ≠ 0. If r = 0, PV = C × n. Excel expresses this through PV(rate,nper,pmt,[fv],[type]), where type 0 means end-of-period payments and type 1 means beginning-of-period payments.

For example, the present value of 12 monthly payments of 500 at 1% per month is approximately 5,627.54:

=PV(1%,12,-500)

The negative payment follows Excel’s cash-flow sign convention: cash paid by the investor is negative and the resulting present value is positive. A change in payment timing or sign convention changes the interpretation.

Loan Payments and Investment Returns

PMT(rate,nper,pv,[fv],[type]) calculates a periodic payment. NPV(rate,value1,value2,...) discounts cash flows beginning at the end of period 1; an initial investment at time zero must be handled separately. IRR(values) estimates the periodic rate that makes net present value equal to zero. XIRR(values,dates) is preferable when dates are irregular.

A financial model should state whether rates are nominal or effective, whether periods are monthly or annual, and whether cash flows occur at the beginning or end of periods.

10. Dynamic Arrays

Dynamic-array functions return multiple results from one formula and automatically spill into adjacent cells. Important functions include:

• FILTER(array,include,[if_empty]) returns records meeting a condition;
• SORT(array,[sort_index],[sort_order]) sorts an array;
• UNIQUE(array) returns distinct values;
• SEQUENCE(rows,[columns],[start],[step]) generates a sequence; and
• SORTBY(array,by_array) sorts one array using another.

For example:

=FILTER(Sales,Sales[Region]=H2,"No records")

returns all rows for the selected region. A spill range is referenced using the hash operator, for example =COUNTA(K2#), where K2 is the formula’s top-left cell.

A #SPILL! error occurs when cells in the intended spill area are not empty, when the formula is inside an Excel Table that cannot accept the result in that form, or when the spill range is otherwise obstructed. Dynamic arrays improve flexibility but require careful layout planning and version compatibility.

11. Formula Errors and Diagnosis

Error handling should not conceal poor data. IFERROR(formula,"Review") is useful when a controlled user-facing message is appropriate, but applying IFERROR indiscriminately can hide genuine errors. A better practice is to validate the underlying cause and report exceptions separately.

Excel’s auditing tools—Trace Precedents, Trace Dependents, Evaluate Formula, Watch Window, and error checking—support systematic review. Testing should include normal, boundary, missing, duplicated, and invalid inputs.

12. Applied Case: Regional Sales and Receivables Review

Background

Illustrative case: Meridian Office Supplies maintains a sales table containing InvoiceID, CustomerID, Region, InvoiceDate, DueDate, Status, Quantity, UnitPrice, and Amount. Management wants a January report showing completed sales by region, overdue invoices, and customers whose credit limits are exceeded.

Problem

The existing workbook contains copied formulas, inconsistent customer codes, and a VLOOKUP approximate-match error. Some January invoices include time values in the InvoiceDate field, so a formula using 31 January as an upper boundary excludes invoices recorded later that day.

Analysis and Approach

First, the amount formula is standardized:

=[@Quantity]*[@UnitPrice]

The January completed-sales measure uses a half-open date interval:

=SUMIFS(Sales[Amount],Sales[Status],"Completed",Sales[InvoiceDate],">="&DATE(2025,1,1),Sales[InvoiceDate],"<"&DATE(2025,2,1))

The regional report uses a region selected in H2:

=SUMIFS(Sales[Amount],Sales[Region],$H$2,Sales[Status],"Completed",Sales[InvoiceDate],">="&DATE(2025,1,1),Sales[InvoiceDate],"<"&DATE(2025,2,1))

The count of overdue invoices at the reporting date in H3 is:

=COUNTIFS(Sales[DueDate],"<"&$H$3,Sales[Status],"<>Paid")

A customer’s credit limit is retrieved by exact match:

=XLOOKUP([@CustomerID],Customers[CustomerID],Customers[CreditLimit],"Missing customer")

The exposure test is:

=IF([@Outstanding]>[@CreditLimit],"Over limit","Within limit")

Finally, an exception list is generated:

=FILTER(Sales,Sales[Status]="Exception","No exceptions")

Outcome

The revised workbook includes a controlled input area, documented date boundaries, exact-match customer retrieval, visible exception reports, and formulas that expand with the table. The VLOOKUP error is eliminated, and invoices recorded on 31 January with times are included correctly.

Learning Points

The case demonstrates that formula correctness depends on data design and business definitions. Exact versus approximate matching, date boundaries, sign conventions, table structure, and exception handling are accounting-control issues, not merely technical preferences.

13. Professional Practice, Ethics, and Governance

Spreadsheet users have a responsibility to preserve data integrity and explain significant assumptions. Good practice includes:

• separating inputs, calculations, and outputs;
• using consistent units, signs, and date conventions;
• protecting formula cells while leaving intended inputs editable;
• documenting external sources, rates, and assumptions;
• using version control and retaining prior approved copies;
• reconciling spreadsheet totals to the general ledger or source system;
• reviewing formulas independently for material reports; and
• restricting access to confidential payroll, customer, and financial data.

An accountant must not alter formulas to achieve a desired result or conceal exceptions with blanket error suppression. AI-assisted spreadsheet generation can help draft formulas, but generated formulas must be tested against known cases, reviewed for data privacy, and approved by a responsible professional. AI does not replace accounting judgment, authorization, or audit evidence.

Chapter Summary

Spreadsheet formulas convert accounting inputs into calculated information. Relative references support copying, absolute references preserve fixed assumptions, and mixed references support two-dimensional models. Arithmetic and comparison operators must be interpreted according to precedence and parentheses. IF, AND, OR, SUMIFS, and COUNTIFS provide conditional logic and aggregation, while XLOOKUP, INDEX-MATCH, and VLOOKUP retrieve related information under different matching assumptions. Text and date functions support data preparation and ageing analysis. Financial functions implement time-value-of-money calculations but require explicit timing, rate, and sign conventions. Dynamic arrays improve reporting flexibility but introduce spill-range considerations. Formula errors should be diagnosed rather than hidden. Reliable spreadsheets also require documentation, reconciliation, access control, independent review, and ethical use.

Key Terms

Relative reference: A cell reference that changes when copied.

Absolute reference: A cell reference whose row and column remain fixed.

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

Operator: A symbol or instruction that performs a calculation or comparison.

Logical test: An expression that evaluates to TRUE or FALSE.

IF function: A function that returns different results according to a condition.

SUMIFS: A function that sums values meeting multiple criteria.

COUNTIFS: A function that counts records meeting multiple criteria.

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

Approximate match: A lookup based on ordered thresholds or the nearest permitted value.

Structured reference: A table-based reference using field names.

Named range: A descriptive name assigned to a cell or range.

Spill range: The cells automatically populated by a dynamic-array formula.

Dynamic array: A formula result containing multiple automatically generated values.

Circular reference: A dependency in which a formula ultimately refers to itself.

Present value: The value today of a future cash flow discounted at a specified rate.

IRR: The periodic rate that makes the net present value of cash flows equal to zero.

Day-count convention: A rule specifying how time is measured for interest calculations.

Formula auditing: Procedures for tracing and testing spreadsheet formulas.

Exception report: A report identifying records that violate defined conditions.

Review Questions

Short Answer Questions

1. What is the difference between a relative reference and an absolute reference?
2. Why should an exact-match argument generally be specified in VLOOKUP?
3. What type of result does COUNTIFS return?
4. What does the #SPILL! error indicate?
5. Why can a less-than-next-month boundary be preferable when filtering dates?

Long Answer Questions

1. Explain how relative, absolute, and mixed references operate, using an accounting schedule to illustrate each.
2. Discuss the construction and auditability of SUMIFS and COUNTIFS formulas.
3. Compare XLOOKUP, VLOOKUP, and INDEX-MATCH, including their matching assumptions and limitations.
4. Explain the assumptions underlying Excel’s PV, PMT, NPV, and IRR functions.
5. Discuss the governance and ethical controls required for a spreadsheet used in financial reporting.

Analytical / Application Questions

1. Design formulas for a monthly report that sums completed sales by region and counts overdue unpaid invoices. State how you would treat date-time values.
2. A lookup returns incorrect tax rates for some income levels. Diagnose possible causes and propose a corrected lookup design.
3. A dynamic FILTER formula returns #SPILL! and a financial ratio returns #DIV/0!. Explain a systematic diagnostic process for both errors.

Multiple Choice Questions with Answers

Question 1

Which reference remains unchanged when copied to another cell?

A. A1

B. $A$1

C. $A1

D. A$1

Correct Answer: B. $A$1

Question 2

Which function sums values satisfying several criteria?

A. COUNTIFS

B. SUMIFS

C. XLOOKUP

D. IFERROR

Correct Answer: B. SUMIFS

Question 3

What does the fourth argument FALSE in VLOOKUP normally request?

A. An approximate match

B. A descending sort

C. An exact match

D. A blank result

Correct Answer: C. An exact match

Question 4

Which formula correctly counts completed sales in the East region?

A. =SUMIFS(Amount,Region,"East",Status,"Completed")

B. =COUNTIFS(Region,"East",Status,"Completed")

C. =XLOOKUP("East",Region,Status)

D. =IF(Region="East",Status="Completed")

Correct Answer: B. =COUNTIFS(Region,"East",Status,"Completed")

Question 5

What is a common cause of #SPILL!?

A. A blocked destination range

B. A missing percentage sign

C. A negative cash flow

D. An exact lookup match

Correct Answer: A. A blocked destination range

Question 6

Which function returns the present value of equal periodic payments under specified rate and timing assumptions?

A. PMT

B. IRR

C. PV

D. XIRR

Correct Answer: C. PV

Question 7

If a formula is copied across columns while row 5 must remain fixed, which reference form is appropriate?

A. A5

B. $A$5

C. $A5

D. A$5

Correct Answer: D. A$5

Question 8

Which function is most suitable for returning all sales records for a selected region in modern Excel?

A. FILTER

B. LEFT

C. EOMONTH

D. PMT

Correct Answer: A. FILTER

Question 9

What does COUNTIFS return?

A. The total monetary value meeting criteria

B. The number of records meeting criteria

C. The first matching text value

D. The discounted value of cash flows

Correct Answer: B. The number of records meeting criteria

Question 10

Why might a formula use InvoiceDate < 1 February rather than InvoiceDate <= 31 January?

A. It excludes all January dates

B. It handles January date-time values more reliably

C. It converts dates into text

D. It requests an approximate lookup

Correct Answer: B. It handles January date-time values more reliably