Excel Formula to Calculate Expense Amount Above a $200 Allowance
Question details
Calculate the portion of an expense that is above a fixed allowance without creating a separate allowance column.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The expense amount is stored in a worksheet cell, and the user wants a formula that returns only the amount over a $200 allowance.
- Observed behavior
- The required result is zero for expenses of $200 or less and the excess amount for expenses greater than $200.
Identify the cell that contains the expense amount. In the examples below, the expense is in cell A2 and the fixed allowance is $200.
Use the MAX Formula to Return Expense Above the $200 Allowance
Use this formula when you need to calculate an excess amount and prevent negative results for expenses that do not exceed the allowance.
The MAX function compares the calculated excess with zero. It returns the larger value, so an expense below the allowance displays 0 rather than a negative amount.
Click the cell where you want the result to appear, such as B2.
Type =MAX(A2-200,0), where A2 is the cell containing the expense amount.
Press Enter. For example, if A2 contains 275, the formula returns 75; if A2 contains 150, it returns 0.
Select the formula cell and drag the fill handle down to copy =MAX(A2-200,0) to other rows. Excel updates the expense reference for each row while keeping 200 fixed.
Use an IF Formula for a More Explicit Allowance Test
Use this alternative when you want the formula to visibly test whether the expense is greater than $200.
Calculate the Portion of an Expense Above a Fixed Allowance in WPS Spreadsheets
WPS Spreadsheets supports the same MAX and IF formulas used to calculate expense overages. Enter the formula once, then fill it down to calculate the excess above a fixed allowance for every expense row.
- 1. Open the expense worksheet: Open your spreadsheet in WPS Office and select the worksheet containing the expense amounts.
- 2. Choose an output cell: Click a blank cell beside the first expense, such as B2 when the expense is in A2.
- 3. Enter the excess formula: Enter =MAX(A2-200,0) in the formula bar or directly in the selected cell.
- 4. Confirm the calculation: Press Enter to display zero for expenses within the allowance or the amount over $200 for larger expenses.
- 5. Copy the formula to remaining rows: Drag the fill handle from the formula cell down the column to calculate overages for all listed expenses.
Frequently Asked Questions
What formula calculates only the amount above $200 in Excel?
If the expense is in A2, use =MAX(A2-200,0). It returns the amount above $200 and returns 0 when A2 is $200 or less.
Why does my expense allowance formula show a negative number?
A direct subtraction formula such as =A2-200 returns a negative value when the expense is below $200. Use =MAX(A2-200,0) to limit the minimum result to zero.
Can I use a different fixed allowance in the formula?
Yes. Replace 200 with the required allowance. For example, =MAX(A2-500,0) calculates the portion of A2 above $500.
How do I calculate the excess for every row in an expense list?
Enter =MAX(A2-200,0) in the first result cell, press Enter, then drag the fill handle down. Each row will use its corresponding expense cell.
Can the allowance be stored in one cell instead of typed into every formula?
Yes. If the allowance is in D1, use =MAX(A2-$D$1,0). The dollar signs keep the allowance reference fixed when you copy the formula down.
