This guide covers everything you need to know about the Google Sheets MINUS function, including its definition, syntax, use cases, and how to use it.
What is the MINUS Function? How Does It Work?
The MINUS function in Google Sheets helps you subtract one number from another. It’s like using the “-” sign in regular math. So, when you put in MINUS(10, 5), Google Sheets will take away 5 from 10 and show the result as 5.
Remember, this function is for numbers only. If you try to use words, dates, or anything that’s not a number, it won’t work. Besides simple subtraction, you can combine MINUS with other functions in Google Sheets for more detailed calculations.
It’s a useful tool for budgeting, grading, or just any math you need to do in your spreadsheet.
MINUS Syntax
The syntax and arguments for the function are as follows:
MINUS(value1, value2)
- “value1” is the number you’re starting with. It can be a number you type in, a reference to a cell that holds a number, or a selection of cells.
- “value2” is the number you want to subtract from “value1”. It can also be a number, a cell reference, or a selection of cells.
Usage notes related to syntax and arguments include:
- Both value1 and value2 must be numbers, cell references containing numbers, or ranges of cells containing numbers. Non-numeric values will result in an error.
- The MINUS function can only take two arguments. If you want to subtract more than two numbers, you will need to use multiple MINUS functions. For example, to subtract three numbers (A, B, and C), you would use the syntax MINUS(MINUS(A, B), C).
- The MINUS function is equivalent to using the “-” operator. For example, MINUS(A2, A3) is the same as A2 – A3.
- The MINUS function is not case-sensitive. MINUS(A2, A3) is the same as minus(A2, A3).
- The MINUS function ignores text values in ranges. If a range of cells contains any text values, those will be ignored, and only the numeric values will be considered.
Examples of How to Use the MINUS Function
Here are some practical examples that illustrate how to use the MINUS function in Google Sheets.
Example #1: Subtracting Two Numbers
If you want to subtract one number from another, you can use the MINUS function. For instance, if you want to subtract 10 from 20, you would enter the following formula into a cell:
=MINUS(20,10)
After pressing Enter, Google Sheets will calculate the formula and display the result, which is 10.
Example #2: Subtracting Cell References
The MINUS function can also refer to the values of other cells. Suppose you have the number 100 in cell A1 and the number 50 in cell B1. You can subtract the value in B1 from the value in A1 by entering the following formula:
=MINUS(A1,B1)
Google Sheets will calculate the formula and display the result, which is 50.
Example #3: Subtracting a Number from a Cell Reference
You can also use the MINUS function to subtract a fixed number from the value in a cell. For example, if you have the number 30 in cell A1 and you want to subtract 15 from it, you can use the following formula:
=MINUS(A1,15)
Google Sheets will calculate the formula and display the result, which is 15.
Example #4: Subtracting the Result of a Function
The MINUS function can also subtract the result of another function. Suppose you have a range of numbers in cells A1 to A5 and want to subtract the smallest number in the range (determined by the MIN function) from the largest number in the range (determined by the MAX function). You could use the following formula:
=MINUS(MAX(A1:A5),MIN(A1:A5))
Google Sheets will first calculate the MAX and MIN functions, and then subtract the result of the MIN function from the result of the MAX function.
Why Is MINUS Not Working? Troubleshooting Common Errors
If you’re using the MINUS function in Google Sheets and encountering errors, it’s important to know what they mean and how to resolve them. Here are some of the common errors you may face, their causes, and, most importantly, their solutions.
#VALUE! Error
Cause: This error is typically due to incorrect data types. The MINUS function in Google Sheets expects numbers as input. If you use non-numeric values, Google Sheets will return a #VALUE! error.
Solution: Always ensure the cells you’re referencing with the MINUS function contain numeric values. If you’re uncertain whether a cell contains a numeric value, you can use the ISNUMBER function to verify.
#REF! Error
Cause: This error occurs when the references in your formula are no longer valid. For instance, if your formula is referencing a cell that has been deleted, Google Sheets will return a #REF! error.
Solution: Check your formula to ensure all cell references are correct. If you’ve deleted a row or column referenced in your formula, you’ll need to either undo the deletion or adjust the formula to reference a valid cell.
#N/A Error
Cause: The #N/A error is displayed when the MINUS function is used as part of a larger formula and that formula is unable to find the value it’s looking for.
Solution: This can be resolved by checking the components of your larger formula. If you’re using a function like VLOOKUP or MATCH in conjunction with MINUS, ensure those functions return the expected results.
#DIV/0! Error
Cause: This error is displayed when a number is divided by zero within your formula. Although the MINUS function doesn’t perform division, this error can occur if used within a larger formula involving division.
Solution: Check the components of your formula to ensure you’re not dividing by zero. If you’re using a function like DIVIDE or a division operator (/) in conjunction with the MINUS function, ensure those functions or operators are not trying to divide by zero.
#NUM! Error
Cause: This error is rare with the MINUS function, as it’s typically caused by calculations that result in a number too large or too small to be represented in Google Sheets.
Solution: If you encounter this error while using the MINUS function, it’s likely due to a larger formula the function is part of. Check your formula to ensure it’s not trying to calculate a number outside the allowable range for Google Sheets.
Using MINUS With Other Google Sheets Functions
Combining the MINUS function with other Google Sheets functions can provide more complex and useful calculations. Let’s explore how it works with various functions.
With SUM
Usage: The SUM function is used to add up a series of numbers in Google Sheets. Combined with the MINUS function, it allows you to subtract one sum from another.
Example: Imagine you have total sales figures for two different months in cells B1 to B10 and C1 to C10. You want to find out the difference in total sales between these two months. You can use the combination of SUM and MINUS functions to achieve this. The formula would look like this:
=SUM(B1:B10) – SUM(C1:C10)
With AVERAGE
Usage: The AVERAGE function calculates the average of a set of numbers. When combined with the MINUS function, you can find the difference between the average of two sets of numbers.
Example: If you want to find out the difference in average sales between two months, you can use the combination of AVERAGE and MINUS functions. If your sales figures for two different months are in cells D1 to D10 and E1 to E10, your formula would look like this:
=AVERAGE(D1:D10) – AVERAGE(E1:E10)
With MAX
Usage: The MAX function returns the highest number in a set of numbers. Combined with the MINUS function, you can find the range of a set of numbers, which is the difference between the highest and lowest number.
Example: If you have a list of numbers in cells F1 to F10 and want to find the range, you can use the combination of MAX, MINUS, and MIN functions. Your formula would look like this:
=MAX(F1:F10) – MIN(F1:F10)
For more details on the MINUS function, check out the official documentation at the Google Docs Editors Help Center.
