Destructive numbers are a pure a part of life, however they could be a ache to work with, particularly in Excel. When you’re attempting so as to add up a column of numbers and certainly one of them is destructive, it might throw off your complete calculation. Fortuitously, there’s a simple option to make a destructive quantity constructive in Excel. Simply comply with these easy steps.
First, choose the cell that comprises the destructive quantity. Then, click on on the “Residence” 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, below “Class,” choose “Customized.” Within the “Sort” subject, enter the next system: "[Red]0;". This system will format the quantity as a constructive quantity, even whether it is really destructive. Click on on the “OK” button to save lots of your adjustments.
Now, the destructive quantity will likely be displayed as a constructive quantity. You should utilize this system to make any destructive quantity constructive. Simply do not forget that the system will solely change the best way the quantity is displayed. It won’t change the precise worth of the quantity. If it’s essential calculate with the destructive quantity, you have to to enter it as a destructive quantity within the system.
Altering the Signal of the Quantity
To vary the signal of a quantity in Excel, you need to use the minus signal (-) or the system ABS(). The minus signal merely adjustments the signal of the quantity, whereas the ABS() system removes the destructive signal and returns absolutely the worth of the quantity. For instance, if in case you have a cell that comprises the quantity -10, you possibly can change the signal to constructive by coming into the next system:
-(-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 | Consequence |
|---|---|---|
| Minus signal | -(-10) | 10 |
| ABS() system | =ABS(-10) | 10 |
Using the ROUNDUP Perform
The ROUNDUP perform 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 perform, you possibly can effortlessly remodel destructive numbers into constructive ones. Here is the way it works:
Firstly, choose the cell containing the destructive quantity you want to convert. Subsequently, navigate to the “Formulation” tab within the ribbon and click on on the “Math & Trig” perform class. Find and choose the ROUNDUP perform from the accessible choices.
Throughout the ROUNDUP perform’s syntax, specify the destructive quantity as the primary argument. For the second argument, enter the a number of to which you wish to spherical up. As an example, if you wish to spherical as much as the closest complete quantity, merely enter 1 because the second argument. When you want 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 destructive quantity to the desired a number of, successfully changing it right into a constructive quantity. This method provides an easy and environment friendly option to deal with and manipulate destructive 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 robust software in Excel that lets you robotically apply formatting to cells primarily based on particular standards. You should utilize conditional formatting to spotlight cells that comprise constructive numbers, making it straightforward to determine them in a dataset.
Making use of Conditional Formatting to Destructive Numbers
To use conditional formatting to spotlight destructive numbers, comply with these steps:
- Choose the vary of cells you wish to format.
- Go to the “Residence” 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 system to find out which cells to format” possibility.
- Within the “Format values the place this system is true” field, enter the next system: =A1<0
- Click on the “Format” button and select the formatting you wish to apply to destructive numbers.
- Click on “OK” to use the conditional formatting rule.
Now, all of the destructive numbers within the chosen vary will likely be highlighted with the formatting you specified.
Extra Data
You can too use conditional formatting to spotlight different sorts of knowledge, resembling:
| 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 possibly can create a customized system utilizing the IF perform. The IF perform evaluates a logical expression and returns a unique 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 destructive quantity constructive utilizing a customized system, you need to use the next system:
“`
=IF(A1<0, -A1, A1)
“`
On this system, A1 is the cell containing the destructive quantity. If the worth in A1 is lower than 0, the system will multiply it by -1 to make it constructive. If the worth in A1 is 0 or larger, the system will merely return the worth in A1.
7. Destructive Numbers to Constructive Numbers Examples
| Destructive 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 possibly can see, the customized system works for all destructive numbers, changing them to their constructive counterparts. This methodology is especially helpful when it’s essential work with numerous destructive values and wish to automate the method of creating them constructive.
How To Make A Destructive Quantity Constructive In Excel
When coping with destructive numbers in Excel, chances are you’ll generally wish 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 perform to a destructive quantity returns its absolute worth, which is all the time constructive. The syntax for the ABS perform is =ABS(quantity), the place “quantity” is the destructive worth you wish to convert. For instance, =ABS(-10) would return 10.
- Multiply by -1: Multiplying a destructive quantity by -1 leads to a constructive worth. This methodology is simple and could be achieved in Excel by merely including a destructive signal (-) earlier than the quantity. As an example, -(-10) would lead to 10.
Folks Additionally Ask About How To Make A Destructive Quantity Constructive In Excel
Can I exploit a system to alter a number of destructive numbers to constructive?
Sure, you need to use the ABS perform in a system to transform a number of destructive numbers to constructive values. For instance, the system =ABS(A1:A10) would convert all destructive numbers within the vary A1:A10 to constructive.
Is there a option to make all destructive numbers in a column constructive?
To make all destructive numbers in a column constructive, you need to use the Discover and Exchange function in Excel. Choose the column, press Ctrl + F to open the Discover and Exchange dialog field, and enter “-1” (with out quotes) within the “Discover what” subject and “1” (with out quotes) within the “Exchange with” subject. Then click on “Exchange All” to alter all destructive numbers to constructive.
