The formula
Excel 365 / Google Sheets (inline call)Excel 365
=LAMBDA(price,cost,(price-cost)/price)(A2,B2)In E2 → 36%
Named function MARGIN(A3,B3)Excel 365
=MARGIN(A3,B3)In E3 → 42.47%
Equivalent formula (any Excel)ExcelGoogle Sheets
=(A2-B2)/A2In E4 → 36%
✓ Tested. All 1 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown. 2 results for formulas tagged Excel 365 were checked with a reference implementation of the documented behavior, because LibreOffice can't run those functions.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Price | Cost | |||
| 2 | 50 | 32 | 36% | ||
| 3 | 19.99 | 11.5 | 42.47% | ||
| 4 | 36% |
LibreOffice Calc can't run LAMBDA, so the LAMBDA results on this page were checked with a reference implementation of the same calculation, and the equivalent plain formula was verified in LibreOffice.
How it works
- price,cost
- Parameter names, used inside the calculation.
- (price-cost)/price
- The calculation (gross margin).
- (A2,B2)
- Inline call: values passed to the parameters.
- Name Manager
- Formulas › Name Manager › New, Name: MARGIN, Refers to: =LAMBDA(price,cost,(price-cost)/price). In Google Sheets use Data › Named functions.
Common errors and fixes
Why does the formula return #CALC!?
A LAMBDA was entered without calling it. Add (A2,B2) after it, or save it as a name.
Why does the formula return #VALUE!?
Wrong number of arguments passed to the function.
Why does the formula return #NAME??
Excel 2021 or older: LAMBDA isn't available.
Does this work in Google Sheets?
The formulas tagged "Google Sheets" work as-is. Formulas tagged only "Excel" may need ARRAYFORMULA or a different function in Sheets.