Discount Function in Google Sheets: A Practical Guide to Calculating Discounts
Master the discount function in google sheets with practical formulas and a custom Apps Script function. Learn built-in formulas, array techniques, and best practices for applying discounts in budgeting and pricing scenarios.
Google Sheets does not include a built-in discount function. You can compute discounts with simple arithmetic or create a custom Apps Script function for more complex rules. This quick answer demonstrates basic discount amount and discounted price calculations using formulas, plus a starter script to extend discounts at scale.
Understanding the discount function in google sheets
The phrase discount function in google sheets often implies a reusable way to compute how much to subtract from a price or what the final price should be after a discount. Since Sheets does not expose a dedicated DISCOUNT function, you compose the logic with core operators, with optional extension via Google Apps Script for more advanced rules. This section shows how to build reliable discount calculations using standard formulas, so you can price items, run promotions, or model budget scenarios consistently across rows and columns.
# Example: discount amount for a single item
# price in B2, discount rate in C2 (as decimal, e.g., 0.15 for 15%)
=B2 * C2# Example: discounted price after applying the rate
=B2 * (1 - C2)- These formulas work with simple percent discounts and are ideal for line-item calculations. If you prefer a single cell to display the final price, combine them as needed.
- When dealing with currencies, consider wrapping results with a currency format or rounding to two decimals for clarity.
Why this approach works: It leverages standard arithmetic and ensures compatibility across Sheets versions and sharing scenarios. In cases where discounts depend on product or customer data, you can expand the logic with LOOKUPs or array formulas.
- Common variations:
- Fixed-amount discount: price minus a constant value, e.g., =MAX(0, B2 - D2) where D2 is a fixed discount amount.
- Tiered discounts: use IF or IFS to apply different rates by price bands (e.g., =IF(B2>=1000, B20.85, B20.9)).
wordCountSection1 : null},
Steps
Estimated time: 60-90 minutes
- 1
Define your data layout and goals
Map out where prices live, where discounts are stored, and what the final output should look like. Decide if you need per-line discounts, tiered pricing, or product-category based rules.
Tip: Sketch a small example in a sheet to validate formulas before scaling. - 2
Implement built-in formulas for common cases
Add formulas to calculate discount amount and discounted price for each row. Use B column for price and C column for rate, then apply the formulas shown in the examples.
Tip: Use cell references that will be easy to copy down the column. - 3
Explore array formulas and named ranges
If you need bulk discounts, switch to ARRAYFORMULA and named ranges to apply the same logic to many rows. You can reference a discount table by category using VLOOKUP.
Tip: Test with a small dataset to avoid performance issues. - 4
Create a custom Apps Script function (optional)
Open Extensions > Apps Script and add a custom function like DISCOUNT(price, rate, mode) to encapsulate discount logic for consistent reuse across sheets.
Tip: Document how to use the function and what mode values mean. - 5
Test, validate, and iterate
Run tests across edge cases (0% discount, 100% discount, negative prices) and ensure formatting is consistent. Iterate as business rules evolve.
Tip: Keep a changelog of discount rules and formulas.
Prerequisites
Required
- Required
- Basic math and formula syntaxRequired
Optional
- Optional
- Knowledge of named ranges and array formulas (optional)Optional
- Currency formatting best practicesOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected cells or formula text | Ctrl+C |
| PastePaste into target cells or editor | Ctrl+V |
| Fill downCopy the formula down a column | Ctrl+D |
| FindSearch within the sheet | Ctrl+F |
FAQ
Does Google Sheets have a built-in DISCOUNT function?
No, Google Sheets does not include a dedicated DISCOUNT function. Discounts are typically implemented with standard arithmetic or extended with Apps Script for more complex logic.
No—there isn’t a built-in discount function; you can either use basic formulas or add a small script for advanced cases.
How do I apply a percentage discount to a list of products in Sheets?
Store prices in one column and discount rates in another, then apply =Price * Rate or =Price * (1 - Rate) in a new column. You can extend this with ARRAYFORMULA for bulk calculations.
Use a simple price times rate formula across your list, and you can fill down or use an array formula for many rows.
What about tiered discounts (volume discounts) in Sheets?
Tiered discounts can be implemented with IF or IFS statements based on price thresholds. For example, =IF(B2>=1000, B2*0.85, B2*0.9) applies 15% off if price is 1000 or more.
Set discount rates by thresholds using IF statements to apply the right rate for each price.
Are Apps Script custom functions suitable for discounts?
Yes. A custom function like DISCOUNT(price, rate, mode) can encapsulate discount logic and be reused across sheets. It is best for complex or reusable rules.
Custom functions work well for repeated discount logic across sheets.
How should I handle currency formatting with discounts?
Format results as currency and consider rounding to two decimals to keep consistency, especially when exporting results to reports.
Keep currency outputs clean with proper formatting and rounding.
The Essentials
- Use built-in formulas for simple discounts
- Leverage Apps Script for complex rules
- Test edge cases and format outputs clearly
- Consider named ranges and array formulas for scalability