Destructive numbers are a pure a part of life, however they could be a ache to work with, particularly in Excel. When you’re making an attempt so as to add up a column of numbers and one in every of them is detrimental, it could actually throw off your whole calculation. Happily, there’s a simple method to make a detrimental quantity constructive in Excel. Simply comply with these easy steps.
First, choose the cell that comprises the detrimental 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” area, enter the next formulation: "[Red]0;". This formulation will format the quantity as a constructive quantity, even whether it is really detrimental. Click on on the “OK” button to avoid wasting your modifications.
Now, the detrimental quantity can be displayed as a constructive quantity. You need to use this formulation to make any detrimental 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 you must calculate with the detrimental quantity, you will have to enter it as a detrimental quantity within the formulation.
Altering the Signal of the Quantity
To alter the signal of a quantity in Excel, you need to use the minus signal (-) or the formulation ABS(). The minus signal merely modifications the signal of the quantity, whereas the ABS() formulation removes the detrimental signal and returns absolutely the worth of the quantity. For instance, if in case you have a cell that comprises the quantity -10, you’ll be able to change the signal to constructive by coming 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 | Outcome |
|---|---|---|
| Minus signal | -(-10) | 10 |
| ABS() formulation | =ABS(-10) | 10 |
Using the ROUNDUP Operate
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 ability of this operate, you’ll be able to effortlessly rework detrimental numbers into constructive ones. This is the way it works:
Firstly, choose the cell containing the detrimental 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.
Throughout the ROUNDUP operate’s syntax, specify the detrimental quantity as the primary argument. For the second argument, enter the a number of to which you wish to spherical up. For example, if you wish to spherical as much as the closest entire 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 detrimental quantity to the desired a number of, successfully changing it right into a constructive quantity. This system provides an easy and environment friendly method to deal with and manipulate detrimental numbers in your Excel spreadsheets.
| ROUNDUP Operate Syntax | Description |
|---|---|
| =ROUNDUP(quantity, a number of) | Rounds the desired quantity as much as the closest a number of. |
Highlighting Optimistic Numbers with Conditional Formatting
Conditional formatting is a robust instrument in Excel that permits you to routinely apply formatting to cells primarily based on particular standards. You need to use conditional formatting to focus on 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 focus on detrimental numbers, comply with these steps:
- Choose the vary of cells you wish 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 wish to apply to detrimental numbers.
- Click on “OK” to use the conditional formatting rule.
Now, all of the detrimental numbers within the chosen vary can be highlighted with the formatting you specified.
Further Info
You can too use conditional formatting to focus on different forms of information, akin to:
| Standards | Components |
|---|---|
| Optimistic numbers | =A1>0 |
| Zero | =A1=0 |
| Textual content | =ISTEXT(A1) |
| Particular worth | =A1=”worth” |
Making a Customized Components
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 detrimental quantity constructive utilizing a customized formulation, you need to use the next formulation:
“`
=IF(A1<0, -A1, A1)
“`
On this formulation, A1 is the cell containing the detrimental 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. Destructive Numbers to Optimistic Numbers Examples
| Destructive Quantity | Optimistic Quantity | Components |
|---|---|---|
| -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 detrimental numbers, changing them to their constructive counterparts. This methodology is especially helpful when you must work with numerous detrimental values and wish to automate the method of constructing them constructive.
How To Make A Destructive Quantity Optimistic In Excel
When coping with detrimental numbers in Excel, you might typically 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 operate to a detrimental quantity returns its absolute worth, which is all the time constructive. The syntax for the ABS operate is =ABS(quantity), the place “quantity” is the detrimental worth you wish to convert. For instance, =ABS(-10) would return 10.
- Multiply by -1: Multiplying a detrimental quantity by -1 ends in a constructive worth. This methodology is simple and may be achieved in Excel by merely including a detrimental signal (-) earlier than the quantity. For example, -(-10) would end in 10.
Folks Additionally Ask About How To Make A Destructive Quantity Optimistic In Excel
Can I take advantage of a formulation to vary a number of detrimental numbers to constructive?
Sure, you need to use the ABS operate in a formulation to transform a number of detrimental numbers to constructive values. For instance, the formulation =ABS(A1:A10) would convert all detrimental numbers within the vary A1:A10 to constructive.
Is there a method to make all detrimental numbers in a column constructive?
To make all detrimental 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” area and “1” (with out quotes) within the “Exchange with” area. Then click on “Exchange All” to vary all detrimental numbers to constructive.
