In Excel, go to Formulas > Name Manager > New. Enter MKT_ROAS as the name, paste the full LAMBDA into Refers to, and save. Use =MKT_ROAS(A2,B2) for one row, =MKT_ROAS(A2:A20,B2:B20) for a traditional range, =MKT_ROAS(A2:.A600,B2:.B600) for trailing Trim References, =MKT_ROAS(A2#,B2#) for spilled ranges, and =MKT_ROAS(A2:.A600,B2:.B600,1) for numeric output.
Marketing
MKT_ROAS
Calculate ROAS in Excel
Calculate return on ad spend from revenue and spend with a reusable Excel marketing function.
Syntax
=MKT_ROAS(REVENUE, SPEND, [NUMERIC])What It Does
What this function calculates
MKT_ROAS calculates return on ad spend for paid media and revenue reporting. It is built to work with exports where revenue and spend can arrive as numbers, numeric text, ranges, Trim References, named ranges, or spilled arrays.
Syntax
Function syntax
Syntax
=MKT_ROAS(REVENUE, SPEND, [NUMERIC])Interactive Demo
Try MKT_ROAS
Edit the revenue or spend below to see how MKT_ROAS recalculates the result.
A
B
C
D
1
Campaign
Revenue
Spend
ROAS
2
3
4
5
6
Arguments
Inputs this function expects
| Argument | Required | Description | Example |
|---|---|---|---|
REVENUE | Required | The attributed revenue, conversion value, or sales value. Can be a direct numeric value, compatible numeric text, single-cell reference, traditional range, named range, trailing Trim Reference, or Microsoft 365 spilled range. | 2400 |
SPEND | Required | The media spend or campaign cost used to generate the revenue. Can be a direct numeric value, compatible numeric text, single-cell reference, traditional range, named range, trailing Trim Reference, or Microsoft 365 spilled range. | 600 |
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
REVENUE2400
SPEND600
Expected result4x
Revenue divided by spend. Add NUMERIC to return 4 instead of 4x when the result must feed other formulas, charts, or threshold logic.
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.
| Revenue | Spend | Campaign |
|---|---|---|
| 1250 | 250 | Search Brand |
| 1680 | 420 | Paid Social |
| 360 | 180 | Display Prospecting |
| 2400 | 600 | Video Awareness |
| 500 | 0 | No Spend Check |
Example Formula
=MKT_ROAS(A2:.A600,B2:.B600)Expected outputWith the sample data pasted at A1, the formula spills formatted text results: 5x, 4x, 2x, 4x, and 500x. Add ,1 for numeric ROAS multipliers. The no-spend row shows why zero-denominator outputs require review.
Use aligned revenue and spend inputs whether they are single cells, fixed ranges, trailing Trim References, named ranges, or spilled arrays.
Result
What the output means
Returns ROAS as text with an x suffix by default, or a numeric multiplier when NUMERIC is supplied. Range inputs spill row by row. Zero spend is substituted with 1 before division, which avoids errors but may create an unusually high result that needs review.
Function Definition
LAMBDA definition
LAMBDA
=LAMBDA(REVENUE,SPEND,[NUMERIC],IF(ISOMITTED(NUMERIC),
ROUND(IFERROR(VALUE(REVENUE),"")/IFERROR(VALUE(IF(SPEND=0,1,SPEND)),""),2)&"x",
ROUND(IFERROR(VALUE(REVENUE),"")/IFERROR(VALUE(IF(SPEND=0,1,SPEND)),""),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.