ASArvin Stewart

Marketing

MKT_CPM

Calculate CPM in Excel

Calculate cost per thousand impressions from campaign spend and impressions using a reusable Microsoft 365 function that supports single cells, ranges, Trim References, spilled arrays, and optional numeric output.

MKT_ library
Syntax
=MKT_CPM(SPEND, IMPRESSIONS, [NUMERIC])

What It Does

What this function calculates

MKT_CPM calculates cost per thousand impressions (CPM) from Spend and Impressions. The function is designed for modern Microsoft 365 Excel and can work with direct values, single-cell references, traditional ranges, trailing Trim References, spilled ranges, named ranges, and compatible numeric text. By default, MKT_CPM returns presentation-ready text prefixed with a dollar sign. When the optional NUMERIC argument is supplied, the function returns a numeric CPM value that can be used in additional calculations. MKT_CPM also includes a zero-impression safeguard. If an Impressions value is 0, the function substitutes 1 as the denominator to avoid a division-by-zero error.

Syntax

Function syntax

Syntax
=MKT_CPM(SPEND, IMPRESSIONS, [NUMERIC])

Interactive Demo

Try MKT_CPM

Edit the spend or impressions below to see how MKT_CPM recalculates the result.

D2=MKT_CPM(B2,C2)
A
B
C
D
1
Campaign
Spend
Impressions
CPM
2
3
4
5
6

Arguments

Inputs this function expects

ArgumentRequiredDescriptionExample
spendRequiredCampaign or media spend. SPEND can be a numeric value, numeric text that VALUE() can interpret, a single cell reference, a fixed cell range, or a Microsoft 365 spilled range reference.$5,000, B2, B2:.B10, or B2#
impressionsRequiredDelivered impressions. IMPRESSIONS can be a numeric value, numeric text that VALUE() can interpret, a single cell reference, a fixed cell range, or a Microsoft 365 spilled range reference.1,000,000, C2, C2:.C10, or C2#
numbericOptionalOptional. Leave this argument blank to return presentation-ready CPM text prefixed with a dollar sign. Enter 1 when you want MKT_CPM to return numeric values that can be used in additional Excel calculations. The documented convention is to use 1 for numeric output.1

Example

Example calculation

Spend$5,000
Impressions1,000,000

Expected result$5

With Spend of 5,000 and 1,000,000 Impressions, MKT_CPM divides Spend by Impressions and multiplies the result by 1,000. 5,000 ÷ 1,000,000 × 1,000 = 5 Because the optional NUMERIC argument is omitted, MKT_CPM prefixes the rounded result with a dollar sign and returns: $5 Using: =MKT_CPM(5000,1000000,1) returns the numeric value: 5

Sample Data

Copy/paste-ready sample data

Sample Campaign Data

Copy this sample dataset and paste it into Excel starting in A1 to test MKT_CPM with multiple campaign rows.

Paste starting in A1

SpendImpressions
50001000000
2750550000
80001600000
1250250000
64002000000
Example Formula
=MKT_CPM(A2:.A600,B2:.B600)

Expected outputMKT_CPM calculates cost per thousand impressions for each corresponding Spend and Impressions value. This example uses Microsoft 365 trailing Trim References: =MKT_CPM(A2:.A600,B2:.B600) The reference can extend through row 600 while Excel trims unused cells from the trailing end of the range. As campaign rows are added or removed, the calculation can adapt without requiring the ending row reference to be rewritten. By default, MKT_CPM returns presentation-ready text prefixed with a dollar sign. To return numeric results for use in additional calculations, enter 1 as the optional third argument: =MKT_CPM(A2:.A600,B2:.B600,1) MKT_CPM converts compatible text values with VALUE(), calculates Spend ÷ Impressions × 1,000, and rounds each result to two decimal places.

MKT_CPM is designed to work with flexible Microsoft 365 references. Supported examples include: Single cells: =MKT_CPM(A2,B2) Traditional ranges: =MKT_CPM(A2:A20,B2:B20) Trailing Trim References: =MKT_CPM(A2:.A600,B2:.B600) Spilled ranges: =MKT_CPM(A2#,B2#) Numeric output: =MKT_CPM(A2:.A600,B2:.B600,1) The optional NUMERIC argument is intended to use 1 when numeric output is required. When omitted, MKT_CPM returns text prefixed with a dollar sign. Spend and Impressions ranges should represent corresponding rows of data. When array-based references are used, make sure the output area contains enough empty cells for Excel to spill the results. To prevent a division-by-zero error, an Impressions value of 0 is treated as 1 by the function. Users should therefore review zero-impression records carefully because the resulting CPM represents the function's fallback calculation rather than a conventional CPM based on delivered impressions.

Result

What the output means

MKT_CPM supports two output modes. Formatted output When NUMERIC is omitted: =MKT_CPM(SPEND,IMPRESSIONS) the function returns text prefixed with a dollar sign, such as: $5 $12.35 $0 This output is useful for presentation-ready reporting. Numeric output When the optional third argument is supplied using the documented value 1: =MKT_CPM(SPEND,IMPRESSIONS,1) the function returns a numeric CPM value without the dollar-sign text prefix. Numeric output can be used in additional calculations such as averages, comparisons, conditional logic, and other formulas. When ranges or dynamic arrays are supplied, MKT_CPM returns corresponding arrays of results that spill into adjacent cells.

Function Definition

LAMBDA definition

LAMBDA
=LAMBDA(SPEND,IMPRESSIONS,[NUMERIC],

IF(ISOMITTED(NUMERIC),

"$"&ROUND(IFERROR(VALUE(SPEND),"")/IFERROR(VALUE(IF(IMPRESSIONS=0,1,IMPRESSIONS)),"")*1000,2),

ROUND(IFERROR(VALUE(SPEND),"")/IFERROR(VALUE(IF(IMPRESSIONS=0,1,IMPRESSIONS)),"")*1000,2)))

Installation

Add this function to Excel

Open Microsoft Excel and go to:

Formulas → Name Manager → New

In the Name field, enter:

MKT_CPM

In the Refers to field, paste the complete LAMBDA definition shown on this page.

Save the function.

MKT_CPM can then be used throughout the workbook like a built-in Excel function.

Examples:

Single cells:

=MKT_CPM(A2,B2)

Traditional ranges:

=MKT_CPM(A2:A20,B2:B20)

Trailing Trim References:

=MKT_CPM(A2:.A600,B2:.B600)

Spilled ranges:

=MKT_CPM(A2#,B2#)

Numeric output:

=MKT_CPM(A2:.A600,B2:.B600,1)

When using ranges or arrays, place the formula in an area with enough empty cells for Excel to spill the resulting values.

Troubleshooting

Common errors

#SPILL!

When MKT_CPM is used with ranges or arrays, Excel needs enough empty cells to return the complete result. Clear any cells blocking the spill range.

#NAME?

Excel does not recognize MKT_CPM. Confirm that the LAMBDA definition was saved in Name Manager and that the function name is spelled correctly.

Numbers stored as text

MKT_CPM uses VALUE() to convert compatible numeric text before performing the calculation. This can be useful with imported campaign reports where numbers may be stored as text.

Zero impressions

If an Impressions value equals 0, MKT_CPM substitutes 1 as the denominator to prevent a division-by-zero error.

This means a zero-impression row can produce an unusually large CPM. Review zero-impression records carefully because this result represents the function's fallback behavior rather than a conventional CPM based on delivered impressions.

Mismatched ranges

When using array-based references, SPEND and IMPRESSIONS should normally represent corresponding records.

For example:

A2:.A600

should normally be paired with:

B2:.B600

Formatted output cannot be used directly as a number

When NUMERIC is omitted, MKT_CPM adds a dollar sign to the result, making the output text.

If the result will be used in additional calculations, use:

=MKT_CPM(SPEND,IMPRESSIONS,1)

Unexpected numeric output

The optional third argument is evaluated based on whether it was supplied. The documented convention is to enter 1 for numeric output and leave the argument blank for formatted output.

Custom Function Request

Need a function that isn't here?

Tell me what you're trying to calculate.

Request a Custom Function