Adverse numbers are a pure a part of life, however they could be a ache to work with, particularly in Excel. In case you’re making an attempt so as to add up a column of numbers and one in every of them is adverse, it will probably throw off your total calculation. Luckily, there’s a straightforward approach to make a adverse quantity constructive in Excel. Simply comply with these easy steps.
First, choose the cell that comprises the adverse 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, underneath “Class,” choose “Customized.” Within the “Kind” subject, enter the next formulation: "[Red]0;". This formulation will format the quantity as a constructive quantity, even whether it is truly adverse. Click on on the “OK” button to save lots of your modifications.
Now, the adverse quantity will probably be displayed as a constructive quantity. You need to use this formulation to make any adverse quantity constructive. Simply do not forget that the formulation will solely change the way in which the quantity is displayed. It won’t change the precise worth of the quantity. If it’s essential to calculate with the adverse quantity, you will want to enter it as a adverse quantity within the formulation.
Altering the Signal of the Quantity
To vary the signal of a quantity in Excel, you should use the minus signal (-) or the formulation ABS(). The minus signal merely modifications the signal of the quantity, whereas the ABS() formulation removes the adverse signal and returns absolutely the worth of the quantity. For instance, when you have a cell that comprises the quantity -10, you may change the signal to constructive by getting into the next formulation:
-(-10)
This can 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 Operate
The ROUNDUP perform 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 ability of this perform, you may effortlessly remodel adverse numbers into constructive ones. Here is the way it works:
Firstly, choose the cell containing the adverse 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 out there choices.
Inside the ROUNDUP perform’s syntax, specify the adverse quantity as the primary argument. For the second argument, enter the a number of to which you wish 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. In case you want 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 adverse quantity to the required a number of, successfully changing it right into a constructive quantity. This system affords an easy and environment friendly approach to deal with and manipulate adverse numbers in your Excel spreadsheets.
| ROUNDUP Operate Syntax | Description |
|---|---|
| =ROUNDUP(quantity, a number of) | Rounds the required quantity as much as the closest a number of. |
Highlighting Optimistic Numbers with Conditional Formatting
Conditional formatting is a strong instrument in Excel that means that you can robotically apply formatting to cells based mostly on particular standards. You need to use conditional formatting to focus on cells that include constructive numbers, making it simple to establish them in a dataset.
Making use of Conditional Formatting to Adverse Numbers
To use conditional formatting to focus on adverse 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 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 wish to apply to adverse numbers.
- Click on “OK” to use the conditional formatting rule.
Now, all of the adverse numbers within the chosen vary will probably be highlighted with the formatting you specified.
Extra Data
You can even use conditional formatting to focus on different forms of knowledge, akin to:
| Standards | Formulation |
|---|---|
| Optimistic numbers | =A1>0 |
| Zero | =A1=0 |
| Textual content | =ISTEXT(A1) |
| Particular worth | =A1=”worth” |
Making a Customized Formulation
If the built-in features do not meet your particular wants, you may create a customized formulation 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 adverse quantity constructive utilizing a customized formulation, you should use the next formulation:
“`
=IF(A1<0, -A1, A1)
“`
On this formulation, A1 is the cell containing the adverse 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 better, the formulation will merely return the worth in A1.
7. Adverse Numbers to Optimistic Numbers Examples
| Adverse Quantity | Optimistic Quantity | Formulation |
|---|---|---|
| -5 | 5 | =IF(A1<0, -A1, A1) |
| -10 | 10 | =IF(A1<0, -A1, A1) |
| -15 | 15 | =IF(A1<0, -A1, A1) |
As you may see, the customized formulation works for all adverse numbers, changing them to their constructive counterparts. This methodology is especially helpful when it’s essential to work with a lot of adverse values and wish to automate the method of creating them constructive.
How To Make A Adverse Quantity Optimistic In Excel
When coping with adverse numbers in Excel, it’s possible 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 here are two widespread strategies:
- Use the ABS Operate: Making use of the ABS perform to a adverse 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 adverse worth you wish to convert. For instance, =ABS(-10) would return 10.
- Multiply by -1: Multiplying a adverse quantity by -1 leads to a constructive worth. This methodology is easy and might be achieved in Excel by merely including a adverse signal (-) earlier than the quantity. As an illustration, -(-10) would lead to 10.
Individuals Additionally Ask About How To Make A Adverse Quantity Optimistic In Excel
Can I exploit a formulation to alter a number of adverse numbers to constructive?
Sure, you should use the ABS perform in a formulation to transform a number of adverse numbers to constructive values. For instance, the formulation =ABS(A1:A10) would convert all adverse numbers within the vary A1:A10 to constructive.
Is there a approach to make all adverse numbers in a column constructive?
To make all adverse numbers in a column constructive, you should use the Discover and Change characteristic 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” subject and “1” (with out quotes) within the “Change with” subject. Then click on “Change All” to alter all adverse numbers to constructive.
