In Excel, go to Formulas > Name Manager > New. Enter MKT_CPC as the name, paste the full LAMBDA into Refers to, and save. Use =MKT_CPC(A2,B2) for one row, =MKT_CPC(A2:A20,B2:B20) for a traditional range, =MKT_CPC(A2:.A600,B2:.B600) for trailing Trim References, =MKT_CPC(A2#,B2#) for spilled ranges, and =MKT_CPC(A2:.A600,B2:.B600,1) for numeric output.
Marketing
MKT_CPC
Calculate CPC in Excel
Calculate cost per click from spend and clicks with reusable Excel LAMBDA logic.
Syntax
=MKT_CPC(SPEND, CLICKS, [NUMERIC])What It Does
What this function calculates
MKT_CPC standardizes cost-per-click calculations across search, social, display, and other paid media exports. It accepts regular numbers or numeric text and can return presentation-ready dollar text or numeric values for follow-on calculations.
Syntax
Function syntax
Syntax
=MKT_CPC(SPEND, CLICKS, [NUMERIC])Interactive Demo
Try MKT_CPC
Edit the spend or clicks below to see how MKT_CPC recalculates the result.
A
B
C
D
1
Campaign
Spend
Clicks
CPC
2
3
4
5
6
Arguments
Inputs this function expects
| Argument | Required | Description | Example |
|---|---|---|---|
SPEND | Required | The campaign, ad set, keyword, or creative spend. Can be a direct numeric value, compatible numeric text, single-cell reference, traditional range, named range, trailing Trim Reference, or Microsoft 365 spilled range. | 420 |
CLICKS | Required | The number of clicks generated by that spend. Can be a direct numeric value, compatible numeric text, single-cell reference, traditional range, named range, trailing Trim Reference, or Microsoft 365 spilled range. | 310 |
NUMERIC | Optional | Optional. Leave blank for presentation-ready text output. Enter 1 to return a numeric value suitable for calculations. The formula checks whether NUMERIC is supplied with ISOMITTED(NUMERIC), not whether the value specifically equals 1. | 1 |
Example
Example calculation
SPEND420
CLICKS310
Expected result$1.35
Spend divided by clicks. Use the formatted default in reports, or add NUMERIC to return a number that can be averaged, filtered, or charted.
Sample Data
Copy/paste-ready sample data
Sample Campaign Data
Copy and paste this campaign dataset into Excel starting at A1, then test the function against the metric columns.
| Spend | Clicks | Campaign |
|---|---|---|
| 250 | 120 | Search Brand |
| 420 | 310 | Paid Social |
| 180 | 45 | Display Prospecting |
| 600 | 900 | Video Awareness |
| 75 | 0 | No Click Check |
Example Formula
=MKT_CPC(A2:.A600,B2:.B600)Expected outputWith the sample data pasted at A1, the formula spills formatted text results: $2.08, $1.35, $4, $0.67, and $75. Add ,1 for numeric CPC values. The final row demonstrates the zero-click denominator convention.
Trim References can extend through row 600; normal ranges and spilled references work when the spend and click arrays are aligned.
Result
What the output means
Returns CPC as formatted dollar text unless NUMERIC is supplied. Dynamic arrays spill one CPC per row. Zero clicks are substituted with 1 before division to keep the output visible, but those rows should be diagnosed before business decisions are made.
Function Definition
LAMBDA definition
LAMBDA
=LAMBDA(SPEND,CLICKS,[NUMERIC],IF(ISOMITTED(NUMERIC),
"$"&ROUND(IFERROR(VALUE(SPEND),"")/IFERROR(VALUE(IF(CLICKS=0,1,CLICKS)),""),2),
ROUND(IFERROR(VALUE(SPEND),"")/IFERROR(VALUE(IF(CLICKS=0,1,CLICKS)),""),2)))Installation
Add this function to Excel
Troubleshooting
Common errors
#SPILL! means Excel cannot place the dynamic-array result; clear blocked cells below or beside the formula. #NAME? usually means the function was not saved in Name Manager or the name was typed differently. Numbers stored as text are generally handled with VALUE(), but non-numeric labels, extra symbols, or incompatible text can still return blanks or unexpected results. If ranges are mismatched, align the numerator and denominator ranges before using dynamic arrays. When the denominator is zero, this function substitutes 1 before division to avoid divide-by-zero errors; that can create unusually large or non-standard results and should be reviewed. Omit NUMERIC for formatted text output; enter 1 for numeric output. Because the formula uses ISOMITTED(NUMERIC), any supplied third argument triggers numeric mode.
Custom Function Request
Need a function that isn't here?
Tell me what you're trying to calculate.