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 detrimental, it may possibly throw off your complete calculation. Thankfully, there’s a simple technique to make a detrimental quantity optimistic in Excel. Simply comply with these easy steps.
First, choose the cell that comprises the detrimental 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, below “Class,” choose “Customized.” Within the “Sort” area, enter the next system: "[Red]0;". This system will format the quantity as a optimistic quantity, even whether it is really detrimental. Click on on the “OK” button to save lots of your modifications.
Now, the detrimental quantity will probably be displayed as a optimistic quantity. You should use this system to make any detrimental quantity optimistic. Simply keep in mind that the system will solely change the way in which the quantity is displayed. It won’t change the precise worth of the quantity. If you’ll want to calculate with the detrimental quantity, you will want to enter it as a detrimental quantity within the system.
Altering the Signal of the Quantity
To alter the signal of a quantity in Excel, you should use the minus signal (-) or the system ABS(). The minus signal merely modifications the signal of the quantity, whereas the ABS() system removes the detrimental signal and returns absolutely the worth of the quantity. For instance, in case you have a cell that comprises the quantity -10, you’ll be able to change the signal to optimistic by coming into the next system:
-(-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 |
Consequence |
| Minus signal |
-(-10) |
10 |
| ABS() system |
=ABS(-10) |
10 |
Using the ROUNDUP Perform
The ROUNDUP operate in Excel is a flexible device 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 rework detrimental numbers into optimistic 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 accessible 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. In case you 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 detrimental quantity to the required a number of, successfully changing it right into a optimistic quantity. This method affords a simple and environment friendly technique to deal with and manipulate detrimental numbers in your Excel spreadsheets.
| ROUNDUP Perform Syntax |
Description |
| =ROUNDUP(quantity, a number of) |
Rounds the required quantity as much as the closest a number of. |
Highlighting Constructive Numbers with Conditional Formatting
Conditional formatting is a strong device in Excel that lets you robotically apply formatting to cells based mostly on particular standards. You should use conditional formatting to focus on cells that include optimistic 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 detrimental numbers, comply with these steps:
- Choose the vary of cells you wish 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 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 detrimental numbers.
- Click on “OK” to use the conditional formatting rule.
Now, all of the detrimental numbers within the chosen vary will probably be highlighted with the formatting you specified.
Extra Data
You may as well use conditional formatting to focus on different kinds of information, comparable to:
| 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 system 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 optimistic utilizing a customized system, you should use the next system:
“`
=IF(A1<0, -A1, A1)
“`
On this system, A1 is the cell containing the detrimental quantity. If the worth in A1 is lower than 0, the system will multiply it by -1 to make it optimistic. If the worth in A1 is 0 or higher, the system will merely return the worth in A1.
7. Adverse Numbers to Constructive Numbers Examples
| Adverse 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 system works for all detrimental numbers, changing them to their optimistic counterparts. This methodology is especially helpful when you’ll want to work with a lot of detrimental values and wish to automate the method of creating them optimistic.
How To Make A Adverse Quantity Constructive In Excel
When coping with detrimental numbers in Excel, it’s possible you’ll generally wish to convert them to optimistic 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 detrimental quantity returns its absolute worth, which is all the time optimistic. 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 leads to a optimistic worth. This methodology is simple and could be achieved in Excel by merely including a detrimental signal (-) earlier than the quantity. For example, -(-10) would lead to 10.
Individuals Additionally Ask About How To Make A Adverse Quantity Constructive In Excel
Can I take advantage of a system to alter a number of detrimental numbers to optimistic?
Sure, you should use the ABS operate in a system to transform a number of detrimental numbers to optimistic values. For instance, the system =ABS(A1:A10) would convert all detrimental numbers within the vary A1:A10 to optimistic.
Is there a technique to make all detrimental numbers in a column optimistic?
To make all detrimental numbers in a column optimistic, you should use the Discover and Substitute characteristic 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 alter all detrimental numbers to optimistic.
