Detrimental numbers are a pure a part of life, however they could be a ache to work with, particularly in Excel. If you happen to’re making an attempt so as to add up a column of numbers and considered one of them is unfavorable, it might throw off your total calculation. Fortuitously, there’s a simple approach to make a unfavorable quantity constructive in Excel. Simply comply with these easy steps.
First, choose the cell that incorporates the unfavorable quantity. Then, click on on the “House” tab within the ribbon and discover the “Quantity” group. Within the “Quantity” group, click on on the “Cells” button and choose “Format Cells.” Within the “Format Cells” dialog field, click on on the “Quantity” tab. Then, beneath “Class,” choose “Customized.” Within the “Sort” area, enter the next formulation: "[Red]0;". This formulation will format the quantity as a constructive quantity, even whether it is really unfavorable. Click on on the “OK” button to avoid wasting your adjustments.
Now, the unfavorable quantity shall be displayed as a constructive quantity. You need to use this formulation to make any unfavorable quantity constructive. Simply do not forget that the formulation will solely change the best way the quantity is displayed. It is not going to change the precise worth of the quantity. If it is advisable to calculate with the unfavorable quantity, you will have to enter it as a unfavorable quantity within the formulation.
Altering the Signal of the Quantity
To vary the signal of a quantity in Excel, you should utilize the minus signal (-) or the formulation ABS(). The minus signal merely adjustments the signal of the quantity, whereas the ABS() formulation removes the unfavorable signal and returns absolutely the worth of the quantity. For instance, in case you have a cell that incorporates the quantity -10, you’ll be able to change the signal to constructive by coming into the next formulation:
-(-10)
This may return the worth 10.
Here’s a desk summarizing the 2 strategies for altering the signal of a quantity in Excel:
| Methodology | Instance | Outcome |
|---|---|---|
| Minus signal | -(-10) | 10 |
| ABS() formulation | =ABS(-10) | 10 |
Using the ROUNDUP Perform
The ROUNDUP operate in Excel is a flexible software that may be utilized to spherical numbers as much as the closest specified a number of. By harnessing the ability of this operate, you’ll be able to effortlessly rework unfavorable numbers into constructive ones. This is the way it works:
Firstly, choose the cell containing the unfavorable quantity you want to convert. Subsequently, navigate to the “Formulation” tab within the ribbon and click on on the “Math & Trig” operate class. Find and choose the ROUNDUP operate from the accessible choices.
Inside the ROUNDUP operate’s syntax, specify the unfavorable quantity as the primary argument. For the second argument, enter the a number of to which you need to spherical up. As an illustration, if you wish to spherical as much as the closest entire quantity, merely enter 1 because the second argument. If you happen to need to spherical as much as the closest tenth, enter 0.1 because the second argument.
Upon coming into the suitable arguments, press the “Enter” key. Excel will immediately spherical up the unfavorable quantity to the desired a number of, successfully changing it right into a constructive quantity. This system gives a simple and environment friendly approach to deal with and manipulate unfavorable numbers in your Excel spreadsheets.
| ROUNDUP Perform Syntax | Description |
|---|---|
| =ROUNDUP(quantity, a number of) | Rounds the desired quantity as much as the closest a number of. |
Highlighting Constructive Numbers with Conditional Formatting
Conditional formatting is a strong software in Excel that permits you to robotically apply formatting to cells primarily based on particular standards. You need to use conditional formatting to spotlight cells that comprise constructive numbers, making it straightforward to establish them in a dataset.
Making use of Conditional Formatting to Detrimental Numbers
To use conditional formatting to spotlight unfavorable numbers, comply with these steps:
- Choose the vary of cells you need to format.
- Go to the “House” tab within the ribbon.
- Click on the “Conditional Formatting” button.
- Choose “New Rule…” from the drop-down menu.
- Within the “New Formatting Rule” dialog field, choose the “Use a formulation to find out which cells to format” possibility.
- Within the “Format values the place this formulation is true” field, enter the next formulation: =A1<0
- Click on the “Format” button and select the formatting you need to apply to unfavorable numbers.
- Click on “OK” to use the conditional formatting rule.
Now, all of the unfavorable numbers within the chosen vary shall be highlighted with the formatting you specified.
Further Data
You may as well use conditional formatting to spotlight different forms of information, corresponding to:
| Standards | Method |
|---|---|
| Constructive numbers | =A1>0 |
| Zero | =A1=0 |
| Textual content | =ISTEXT(A1) |
| Particular worth | =A1=”worth” |
Making a Customized Method
If the built-in capabilities do not meet your particular wants, you’ll be able to create a customized formulation utilizing the IF operate. The IF operate evaluates a logical expression and returns a distinct worth relying on whether or not the expression is TRUE or FALSE.
Syntax
IF(logical_test, value_if_true, value_if_false)
Instance
To make a unfavorable quantity constructive utilizing a customized formulation, you should utilize the next formulation:
“`
=IF(A1<0, -A1, A1)
“`
On this formulation, A1 is the cell containing the unfavorable quantity. If the worth in A1 is lower than 0, the formulation will multiply it by -1 to make it constructive. If the worth in A1 is 0 or higher, the formulation will merely return the worth in A1.
7. Detrimental Numbers to Constructive Numbers Examples
| Detrimental Quantity | Constructive Quantity | Method |
|---|---|---|
| -5 | 5 | =IF(A1<0, -A1, A1) |
| -10 | 10 | =IF(A1<0, -A1, A1) |
| -15 | 15 | =IF(A1<0, -A1, A1) |
As you’ll be able to see, the customized formulation works for all unfavorable numbers, changing them to their constructive counterparts. This technique is especially helpful when it is advisable to work with numerous unfavorable values and need to automate the method of creating them constructive.
How To Make A Detrimental Quantity Constructive In Excel
When coping with unfavorable numbers in Excel, you could generally need to convert them to constructive values. There are a number of methods to do that, relying in your particular wants. Listed below are two widespread strategies:
- Use the ABS Perform: Making use of the ABS operate to a unfavorable quantity returns its absolute worth, which is at all times constructive. The syntax for the ABS operate is =ABS(quantity), the place “quantity” is the unfavorable worth you need to convert. For instance, =ABS(-10) would return 10.
- Multiply by -1: Multiplying a unfavorable quantity by -1 leads to a constructive worth. This technique is simple and could be achieved in Excel by merely including a unfavorable signal (-) earlier than the quantity. As an illustration, -(-10) would lead to 10.
Individuals Additionally Ask About How To Make A Detrimental Quantity Constructive In Excel
Can I take advantage of a formulation to vary a number of unfavorable numbers to constructive?
Sure, you should utilize the ABS operate in a formulation to transform a number of unfavorable numbers to constructive values. For instance, the formulation =ABS(A1:A10) would convert all unfavorable numbers within the vary A1:A10 to constructive.
Is there a approach to make all unfavorable numbers in a column constructive?
To make all unfavorable numbers in a column constructive, you should utilize the Discover and Substitute function in Excel. Choose the column, press Ctrl + F to open the Discover and Substitute dialog field, and enter “-1” (with out quotes) within the “Discover what” area and “1” (with out quotes) within the “Substitute with” area. Then click on “Substitute All” to vary all unfavorable numbers to constructive.
