Detrimental numbers are a pure a part of life, however they could be a ache to work with, particularly in Excel. For those who’re making an attempt so as to add up a column of numbers and certainly one of them is damaging, it could throw off your total calculation. Luckily, there’s a simple method to make a damaging quantity optimistic in Excel. Simply comply with these easy steps.
First, choose the cell that incorporates the damaging quantity. Then, click on on the “Dwelling” 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” discipline, enter the next formulation: "[Red]0;". This formulation will format the quantity as a optimistic quantity, even whether it is really damaging. Click on on the “OK” button to save lots of your adjustments.
Now, the damaging quantity can be displayed as a optimistic quantity. You should use this formulation to make any damaging quantity optimistic. Simply do not forget that the formulation will solely change the way in which the quantity is displayed. It is not going to change the precise worth of the quantity. If it’s good to calculate with the damaging quantity, you’ll need to enter it as a damaging quantity within the formulation.
Altering the Signal of the Quantity
To alter 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 damaging 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 optimistic by getting into the next formulation:
-(-10)
It will return the worth 10.
Here’s a desk summarizing the 2 strategies for altering the signal of a quantity in Excel:
| Technique | Instance | End result |
|---|---|---|
| Minus signal | -(-10) | 10 |
| ABS() formulation | =ABS(-10) | 10 |
Using the ROUNDUP Perform
The ROUNDUP operate in Excel is a flexible instrument that may be utilized to spherical numbers as much as the closest specified a number of. By harnessing the facility of this operate, you’ll be able to effortlessly remodel damaging numbers into optimistic ones. This is the way it works:
Firstly, choose the cell containing the damaging 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 obtainable choices.
Inside the ROUNDUP operate’s syntax, specify the damaging quantity as the primary argument. For the second argument, enter the a number of to which you need 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. For those who need to spherical as much as the closest tenth, enter 0.1 because the second argument.
Upon getting into the suitable arguments, press the “Enter” key. Excel will immediately spherical up the damaging quantity to the desired a number of, successfully changing it right into a optimistic quantity. This system provides an easy and environment friendly method to deal with and manipulate damaging 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 instrument in Excel that permits you to robotically apply formatting to cells based mostly on particular standards. You should use conditional formatting to spotlight cells that comprise optimistic numbers, making it simple to determine them in a dataset.
Making use of Conditional Formatting to Detrimental Numbers
To use conditional formatting to spotlight damaging numbers, comply with these steps:
- Choose the vary of cells you need to format.
- Go to the “Dwelling” 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” choice.
- 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 damaging numbers.
- Click on “OK” to use the conditional formatting rule.
Now, all of the damaging numbers within the chosen vary can be highlighted with the formatting you specified.
Further Data
You may as well use conditional formatting to spotlight different varieties of information, resembling:
| Standards | System |
|---|---|
| Constructive numbers | =A1>0 |
| Zero | =A1=0 |
| Textual content | =ISTEXT(A1) |
| Particular worth | =A1=”worth” |
Making a Customized System
If the built-in features 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 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 damaging quantity optimistic utilizing a customized formulation, you should utilize the next formulation:
“`
=IF(A1<0, -A1, A1)
“`
On this formulation, A1 is the cell containing the damaging quantity. If the worth in A1 is lower than 0, the formulation will multiply it by -1 to make it optimistic. If the worth in A1 is 0 or larger, the formulation will merely return the worth in A1.
7. Detrimental Numbers to Constructive Numbers Examples
| Detrimental Quantity | Constructive Quantity | System |
|---|---|---|
| -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 damaging numbers, changing them to their optimistic counterparts. This methodology is especially helpful when it’s good to work with a lot of damaging values and need to automate the method of constructing them optimistic.
How To Make A Detrimental Quantity Constructive In Excel
When coping with damaging numbers in Excel, chances are you’ll typically need to convert them to optimistic values. There are a number of methods to do that, relying in your particular wants. Listed below are two frequent strategies:
- Use the ABS Perform: Making use of the ABS operate to a damaging quantity returns its absolute worth, which is at all times optimistic. The syntax for the ABS operate is =ABS(quantity), the place “quantity” is the damaging worth you need to convert. For instance, =ABS(-10) would return 10.
- Multiply by -1: Multiplying a damaging quantity by -1 ends in a optimistic worth. This methodology is easy and might be achieved in Excel by merely including a damaging signal (-) earlier than the quantity. As an example, -(-10) would end in 10.
Individuals Additionally Ask About How To Make A Detrimental Quantity Constructive In Excel
Can I exploit a formulation to vary a number of damaging numbers to optimistic?
Sure, you should utilize the ABS operate in a formulation to transform a number of damaging numbers to optimistic values. For instance, the formulation =ABS(A1:A10) would convert all damaging numbers within the vary A1:A10 to optimistic.
Is there a method to make all damaging numbers in a column optimistic?
To make all damaging numbers in a column optimistic, you should utilize the Discover and Change function in Excel. Choose the column, press Ctrl + F to open the Discover and Change dialog field, and enter “-1” (with out quotes) within the “Discover what” discipline and “1” (with out quotes) within the “Change with” discipline. Then click on “Change All” to vary all damaging numbers to optimistic.
