UMINUS in Google Sheets

What is UMINUS in Google Sheets?

UMINUS in Google Sheets is a unary operator function that changes the sign of a number; it converts a positive number to a negative and vice versa. Its primary function is to negate a value by changing its sign. When we use it with a positive number, it will return its negative equivalent and vice versa. The UMINUS function is not as widely used as other functions like the SUM function, but it is very useful in many mathematical operations.

In this article, we will explore the UMINUS function in detail, learn its syntax, and use it in some practical examples.

As an introduction, we see this simple example. Here, UMINUS(25) will return -25.

=UMINUS(-1) will return 1.

Syntax

Using the UMINUS function is as easy as it can get! Here’s the syntax of the UMINUS in Google Sheets.

=UMINUS(value)

Here, value is the number or the reference to a number you want to negate.

For example, when you enter =UMINUS(15), you get 5 as a result.

It is very useful in many scenarios such as displaying credits and debits where the credits are positive values and debits are negative values.

How to Use UMINUS Function in Google Sheets?

Most of the formulas in Google Sheets are known for their simplicity in use and UMINUS is no different. You can use the function in two ways.

Enter UMINUS manually
Enter through the Google menu

Enter UMINUS Manually

In this example, we will use the UMINUS function to change the signs of numbers in a column.

Assume you have the following values in column A, as shown below. Positive numbers will become negative, and negative numbers will become positive.

Step 1: In cell B1, we enter the following formula.

=UMINUS(A1). Press Enter.

Step 2: Observe that cell B1 will display -4, as the positive number 4 in A1 has been converted to -4.

Drag the fill handle down to cell B4. It will copy the formula to cells B2, B3, and B4. Observe how UMINUS works.

Entering the UMINUS through the Menu Bar.

Place the cursor where you want the formula to be entered.
Go to the Insert tab in Google Sheets.
Select the option Function and then Operator.
From the list of functions, select the UMINUS function.

Examples

As seen before, UMINUS is a function used to negate or return the opposite of a number. This function is used in mathematical operations or for adjusting the signs of values dynamically in a spreadsheet. Let us look at some interesting UMINUS in Google Sheets examples.

Example #1

In this interesting example, let us track the prices of stocks in a portfolio. We wish to calculate the change in stock prices across two days and must use the negative value if the price has dropped.

Step 1: Enter the details as shown below.

Step 2: Let us calculate the change in price from Day 1 to Day 2 by subtracting the prices.

=C2 – B2

However, if the price has dropped, we must show a negative number. Here, we can use UMINUS to make sure that any price decrease is displayed as a negative number, even if it’s already negative.

Enter the following formula:

=UMINUS(C2 – B2)

Explanation:

C2 – B2: It calculates the change in price between Day 2 and Day 1. UMINUS ensures that the result is always negative. If the price drops, it turns the positive result negative. If it goes up, it turns the negative result positive.

Step 3: Press Enter and drag the formula to the other stocks as well.

By using UMINUS, we make sure that the price changes are always shown as negative, even if the price goes up. It can be particularly useful for scenarios where you’re tracking losses or changes in a consistent negative format.

Example #2 – Using UMINUS with ARRAYFORMULA Function

We use the UMINUS function combined with ARRAYFORMULA to apply the sign change to an entire range or array of values simultaneously. Without the ARRAYFORMULA, we will have to do it cell by cell. In this example, if we have a column of numbers in A2:A10 and we wish to negate all the numbers at once, we use the ARRAYFORMULA to wrap up the function.

Step 1: Enter the values in a sheet.

Step 2: Now, apply the UMINUS in Google Sheets function to the range for negation. Wrap it up with ARRAYFORMULA as shown below.

=ARRAYFORMULA(UMINUS(A2:A10))

Step 3: Press Enter and observe the results.

This method makes it easier to perform operations over large sets of data without needing to manually apply formulas to each individual cell.

Example #3 – Combining UMINUS with SUM Function

This is an interesting example of how to use the UMINUS combined with the SUM function in Google Sheets. In this example, we have the income and expenses for a month in two columns.

Step 1: Enter the expenses and income for a month in two columns.

Column A (A2:A6) contains income

Column B (B2:B6) contains the expenses.

Now, to calculate the net savings, we use a way that subtracts the total expenses from income directly using the UMINUS function.

Step 2: Enter the following formula in Google Sheets

=SUM(A2:A6) + UMINUS(SUM(B2:B6))

Explanation:

SUM(A2:A6): This sums the income.
SUM(B2:B6): This sums the expenses.
UMINUS(SUM(B2:B6)): This formula negates the sum of expenses, making it a negative value.

Step 3: Press Enter. Here, the expenses are automatically subtracted from the income, and we can dynamically track the total savings without manually entering negative values.

Using UMINUS in this way provides a dynamic solution. It allows you to adjust the calculation based on the changing values in the expenses column, making your financial tracking more efficient and without errors.

Important Things to Note

One aspect of UMINUS which is extremely useful is its ability to automatically negate a value without manually changing the sign. It can be especially helpful in financial calculations and data analysis.
It’s widely used when flipping the sign of a result from a formula and is particularly helpful when dealing with expenses or debts.
It’s very useful when used with functions like ARRAYFORMULA or SUM, allowing users to apply negation to entire ranges of data without needing to adjust individual cells.
Using UMINUS with SUM can be used to calculate the opposite of a total sum, such as calculating negative profits or adjusting values dynamically.

Frequently Asked Questions (FAQs)

What are some use case scenarios in real time for UMINUS?

UMINUS is useful in financial spreadsheets for quickly calculating losses or adjusting values. We use it in profit and loss statements, where expenses are typically subtracted from income, but you want to show them as negative values. You can also use them to show credit values as positive and debit values as negative. By using UMINUS, you streamline the process and avoid manually editing negative numbers.

Can we use UMINUS with a range of cells?

Yes, we can use UMINUS with ranges especially when combined with ARRAYFORMULA. For example, if you have a range of values in column A (A2:A5), you can apply UMINUS to all of them at once using.

=ARRAYFORMULA(UMINUS(A2:A5)). It will negate all the values in the range.

Why use UMINUS instead of a negative sign?

UMINUS is particularly useful when working with cell references, arrays, or formulas where manually typing the negative sign isn’t practical or desired.

For example, if you have a formula that calculates a value and you want to represent it as a negative number, UMINUS can handle this automatically.

Download Template

This article must help understand UMINUS in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

UMINUS in Google Sheets Download