Excel Formula to Add or Subtract Values From a Fixed Cell
Question details
Create a formula that uses a fixed starting amount and adds or subtracts an amount according to a plus or minus indicator in another cell.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to copy a formula down a worksheet while keeping the starting-value cell unchanged.
- Observed behavior
- The formula should retain the same starting cell reference and apply either a positive or negative adjustment based on the indicator.
Confirm which cell contains the fixed starting amount, which cell contains the plus or minus indicator, and which cell contains the adjustment amount. Use a plus sign (+) and minus sign (-) consistently in the indicator column.
Use an absolute reference with an IF formula
Use this method when one cell contains the fixed starting amount and each row has an indicator that determines whether to add or subtract an amount.
An absolute reference, such as $A$1, does not change when you copy the formula to other rows. The IF function checks the operator in column B and returns either the amount or its negative value.
Type the starting value in cell A1. This is the value that must remain fixed in every copied formula.
Enter + or - in cell B2, then enter the amount to apply in cell C2.
Select the result cell, such as D2, and enter =$A$1+IF(B2="+",C2,-C2). Press Enter to calculate the result.
Drag the fill handle from D2 downward or use Copy and Paste. The $A$1 reference remains fixed, while B2 and C2 change for each row.
Handle plus, minus, and blank indicator cells explicitly
Use this version when indicator cells can be blank and you do not want a blank row to subtract an amount.
Use WPS Spreadsheets to Add or Subtract From a Fixed Starting Cell
WPS Spreadsheets supports absolute references and IF formulas, so you can keep a starting amount fixed and calculate row-by-row additions or subtractions from plus and minus indicators.
- 1. Open the workbook in WPS Spreadsheets: Open the Excel workbook in WPS Office and select the worksheet containing the starting amount and adjustment rows.
- 2. Identify the fixed value cell: Click the cell containing the starting amount, such as A1, and use $A$1 in the formula to lock both its column and row.
- 3. Enter the conditional calculation: Click the first result cell and enter =$A$1+IF(B2="+",C2,-C2), adjusting B2 and C2 to match your sign and amount columns.
- 4. Fill the formula down: Drag the fill handle on the selected result cell down the column to calculate the remaining rows.
Frequently Asked Questions
Why does my fixed cell reference change when I copy an Excel formula?
A relative reference such as A1 changes when copied. Change it to $A$1 to lock both the column and row, or press F4 while editing the reference in the formula bar.
How do I subtract an amount when another cell contains a minus sign?
Use an IF formula that returns a negative value for the minus sign, such as =$A$1+IF(B2="+",C2,-C2). With B2 set to -, the formula subtracts C2.
What formula should I use if the sign cell can be blank?
Use =$A$1+IF(B2="+",C2,IF(B2="-",-C2,0)). A blank or unsupported indicator returns 0, so no amount is added or subtracted.
Can I use this formula in WPS Spreadsheets?
Yes. WPS Spreadsheets supports Excel-style absolute references, IF functions, and .xlsx or .xls workbook formats.
