How to Subtract Numbers and Show Positive or Negative Results
Question details
The user wants to subtract two numbers and retain the positive or negative sign, instead of just finding the absolute difference.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating the difference between two values (such as a physical count and an on-hand quantity) and visually differentiating positive and negative outcomes.
- Observed behavior
- Using MIN and MAX functions to find the difference strips the directionality, always returning a positive absolute difference instead of the actual positive or negative variance.
Verify that your starting values and ending values are clearly separated into two distinct columns, and ensure the cells are formatted as 'General' or 'Number'.
Use a Basic Subtraction Formula with Custom Formatting
Apply a simple cell-minus-cell formula to maintain the correct mathematical sign, then use conditional formatting or custom number formats to color-code the results.
To preserve the direction of your subtraction (whether it increased or decreased), you must avoid absolute value formulas or combinations of MIN and MAX. A standard subtraction formula accurately yields positive and negative results.
Select the cell where you want the variance result to appear. Type the equals sign followed by the standard subtraction formula, for example `=E8-D8`, and press Enter. This ensures the correct sign is preserved.
Select the result cell, hover over the bottom-right corner until the cursor changes to a crosshair (fill handle), and drag it down the column to apply the formula to the rest of your dataset.
Highlight all your result cells. Navigate to the Home tab on the top ribbon, click on 'Conditional Formatting', hover over 'Highlight Cells Rules', and select 'Less Than'. Type '0' in the dialog box and choose 'Red Text' from the dropdown. Repeat the process using 'Greater Than' 0 to apply a green format for positive results.
Easily Calculate and Format Differences in WPS Office
WPS Spreadsheet offers powerful formula calculations and intuitive conditional formatting to easily subtract numbers and visualize positive and negative trends effortlessly.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office, open your spreadsheet file, and locate the columns containing the numbers you wish to subtract.
- 2. Apply the subtraction formula: In an empty column, type `=cell1-cell2` (e.g., `=E8-D8`) and hit Enter to get the exact difference, preserving the negative or positive sign. Drag the fill handle to apply it downward.
- 3. Highlight negative and positive numbers: Navigate to the 'Home' tab, click 'Conditional Formatting', and use 'Highlight Cells Rules' to set values greater than zero in green, and values less than zero in red.
Frequently Asked Questions
Why is my subtraction formula returning a positive number when it should be negative?
If you are using functions like ABS(), MAX(), or MIN() to calculate the difference, Excel forces the result to be a positive absolute value. Ensure you are using a standard subtraction formula like `=A1-B1` to retain negative results.
How can I display negative numbers in parentheses instead of a minus sign?
Select the cells you want to format, press Ctrl+1 to open the Format Cells dialog box. Go to the 'Number' tab, select 'Number' from the category list, and choose the option displaying red or black numbers inside parentheses, such as (1234).
Can I automatically change the cell background color instead of just the text color?
Yes. Select your formula results, navigate to the Home tab, click 'Conditional Formatting' > 'Highlight Cells Rules'. When setting the condition for 'Greater Than' or 'Less Than', choose a 'Custom Format' from the dropdown and switch to the 'Fill' tab to pick a background color.
