When would I actually use MDETERM in a spreadsheet?

MDETERM appears in linear algebra, systems of linear equations, and advanced financial modeling (option pricing, portfolio optimization, covariance matrix analysis). Most day-to-day spreadsheet work uses aggregation functions like SUMIF instead.

What does a determinant of 0 mean?

A zero determinant indicates a singular matrix—one with no unique inverse, where its rows or columns are linearly dependent. This is a valid result signaling that the system of equations it represents has either no solution or infinitely many solutions.

Why do I get #VALUE! when I include date columns in my array?

Dates are stored as numbers in spreadsheets, but MDETERM tries to perform exact matrix operations on them, which often produces unexpected results or errors. Always use purely numeric columns (Order ID, Units, Unit Price) and exclude date and text columns.

Can MDETERM work on a 1×1 matrix?

Yes. =MDETERM(D2:D2) returns the single value in that cell because a 1×1 matrix's determinant is simply its only element. This is mathematically valid but rarely useful in practice.

MDETERM function

MDETERM calculates the determinant of a square matrix, returning a single scalar value used in linear algebra and specialized financial modeling calculations.

=MDETERM(array)

Generate a MDETERM formula

Describe what you need. The generator will reach for MDETERM where MDETERM is the right tool, and tell you when it is not.

Arguments

How MDETERM reads its arguments

Argument	Required	Description
array	Required	A square array of numeric values (n×n where rows equal columns); text and dates cause #VALUE! error; non-square ranges are rejected.

Returns

A single numeric value representing the matrix determinant; returns 0 for singular matrices and errors for non-square arrays.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate 2×2 determinant from sales units and prices

Units	Unit Price
5	25
8	15

=MDETERM(D2:E3)

Result: -125

The first order (101, North) had 5 units at $25 each; the second order (102, South) had 8 units at $15 each. The determinant is (5 × 15) − (8 × 25) = 75 − 200 = −125. A negative determinant indicates the matrix has a left-handed orientation.

2. Calculate 3×3 determinant of order metrics

Order ID	Units	Unit Price
101	5	25
102	8	15
103	3	40

=MDETERM(A2:C4)

Result: 2150

Using order IDs 101–103 as the first column, units 5, 8, 3 as the second, and unit prices $25, $15, $40 as the third, the 3×3 determinant calculates to 2150. This demonstrates how MDETERM handles larger square matrices, which is useful in linear algebra for checking matrix invertibility.

3. Determinant of later orders subset

Units	Unit Price
3	40
6	20

=MDETERM(D4:E5)

Result: -180

Orders 103 and 104 (East and West regions) had 3 and 6 units at $40 and $20 respectively. The determinant is (3 × 20) − (6 × 40) = 60 − 240 = −180. Different subsets of the same dataset yield different determinants; this isolates a specific pair of transactions.

Common errors

Which MDETERM error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	Array is not square. MDETERM requires rows = columns; a 2×3 range (such as D2:F3) fails because it has 2 rows but 3 columns.	Select a square range only, such as D2:E3 (2×2) or A2:C4 (3×3). If using named ranges, verify dimensions before passing to MDETERM.
#VALUE!	Array contains non-numeric values. Text (Region names like 'North'), dates, or booleans in the range break the calculation; MDETERM reads only pure numbers.	Exclude text and date columns. Use numeric-only columns such as Order ID, Units, and Unit Price. Extract the numeric portion of dates if needed (e.g., =DAY(date_cell)).
#NUM!	Severe numerical instability or matrix properties prevent accurate calculation. Extremely large values, very small values near precision limits, or a nearly-singular matrix can trigger this.	Scale your data (divide by 1000 if numbers are in millions, then adjust result). Check that your matrix represents a well-conditioned system; avoid matrices where one row is nearly a multiple of another.

Tips and when to use something else

MDETERM only accepts square matrices; if rows ≠ columns, you get #VALUE!. Always double-check your range dimensions before calling it.
For most sales analytics and business reporting, SUMIF and SUMIFS are more appropriate than matrix operations. Reserve MDETERM for linear algebra use cases: solving systems of equations, checking matrix invertibility, or specialized financial models.
A determinant of exactly 0 is valid output, not an error—it means the matrix is singular and cannot be inverted. This is useful for detecting linear dependence among your rows or columns.
Avoid single-column ranges like =MDETERM(D:D); a column is not a square array. Also avoid mixing your data matrix with headers; start your range at the first numeric row.

Related functions

TRANSPOSETRANSPOSE flips a row-or-column array, turning rows into columns and columns into rows, returning a spilled array of the same size swapped.MMULTMMULT multiplies two matrices and returns the resulting matrix, performing linear algebra calculations for transformation and scaling operations.SUMPRODUCTSUMPRODUCT multiplies arrays element-by-element and sums the results, useful for weighted totals and multi-criteria counting.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.SUMIFSSUMIFS returns the sum of cells that meet multiple criteria, making it ideal for aggregating data with several conditions at once.

Frequently asked questions

When would I actually use MDETERM in a spreadsheet?: MDETERM appears in linear algebra, systems of linear equations, and advanced financial modeling (option pricing, portfolio optimization, covariance matrix analysis). Most day-to-day spreadsheet work uses aggregation functions like SUMIF instead.
What does a determinant of 0 mean?: A zero determinant indicates a singular matrix—one with no unique inverse, where its rows or columns are linearly dependent. This is a valid result signaling that the system of equations it represents has either no solution or infinitely many solutions.
Why do I get #VALUE! when I include date columns in my array?: Dates are stored as numbers in spreadsheets, but MDETERM tries to perform exact matrix operations on them, which often produces unexpected results or errors. Always use purely numeric columns (Order ID, Units, Unit Price) and exclude date and text columns.
Can MDETERM work on a 1×1 matrix?: Yes. =MDETERM(D2:D2) returns the single value in that cell because a 1×1 matrix's determinant is simply its only element. This is mathematically valid but rarely useful in practice.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17