Minus Formula in Excel
We do not have any inbuilt formula for subtraction or minus in Excel. We use the minus operator (-) to do the arithmetical subtraction in Excel. To subtract two values from each other, we also need to use the equals to an operator to make minus a formula. For example, = value 1- value 2 is a minus formula where we subtract value 1 from value 2. Note that the values should be in the same format to avoid formula errors.
For example, suppose we have a data set. In column A1 we have 150, and in column B, we have -30. Then, if we enter the formula A1-B1 into a cell, the cell will return the result as 150-30 = 120 in column C1.
Table of contents
How to Use Minus Formula in Excel?
For adding two numbers, we have the SUM function in excelSUM Function In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges..
However, we do not have any subtraction formula for minus numbers in Excel. A formula should start with an equal sign in the targeted cell.
Assume we have 5 in cell A1 and 3 in cell B1. We want to subtract the value in B1 from A1. Moreover, we want the result to be shown in cell C1. Therefore, the formula in cell C1 should read like this: =A1 – B1.
We can also directly insert the values to cell C1 itself.
Even though we can directly enter the numbers into the formula, it is always recommended to give cell references. Because giving cell references makes the formula dynamic and updates the value automatically if there are any changes.
Examples of Minus Calculation
We have data on Budgeted Cost vs. Actual Cost for 2018. The finance department supplied these two data. We need to find out the variance amount and whether the budgeted cost is within the limit or not?
How do we find out the variance amount? First, we need to deduct the budgeted cost from the actual cost.
- We first need to deduct cell B2 from A2 using the minus formula.
- We need to press the “Enter” key to see the result.
- Now, drag the minus formula to cell C9 for the other values to be determined.
From the above result, it is clear that only one item is within the budget, i.e., the C6 cell. Therefore, if the variance is negative, it is over the budgeted number, and if the variance is positive, it is within the budgeted number.
We know the basics of minus calculation. We will deal with one negative number and one positive number in this example.
We have data on quarterly profit and loss numbers.
We need to find the variance from Q1 to Q2, Q3 to Q4, and Q5 to Q6.
The usual formula should be Variance = Q1 – Q2, Variance = Q3 – Q4, Variance = Q5 – Q6.
The first variance in the Q1 loss was -150000. The second Q2 profit is 300000, and the overall variance should be a profit of 150000. But the formula displays -450000. It is one of the drawbacks of blindly using the minus formula in Excel.
We need to alter the formula here. Rather than subtracting Q1 from Q2, we need to add Q1 to Q2. In addition, we need to include the plus sign here since there are positive and negative numbers. The basic of mathematics is plus * minus = minus.
The third variance in the Q5 loss was -75000, and the Q6 loss was -125000. Therefore, the variance should be -50000, not +50000.
We need to subtract in excelSubtract In ExcelThe subtraction formula in excel facilitates the subtraction of numbers, cells, percentages, dates, matrices, times, and so on. It begins with the comparison operator “equal to” (=) followed by the first number, the minus sign, and the second number. For example, to subtract 2 and 5 from 15, apply the formula “=15-2-5.” It returns 8. the Q6 value from the Q5 value. Since there are two negative numbers, we need to take the highest negative number, and from that number, we need to deduct the other negative number.
In this way, we can do the subtraction operations in Excel. You should know the basics of mathematics to deal with these situations.
Below is the data are given to me by the sales manager. This data is the individual sales data for his team. The data includes individual targets and individual actual sales contributions.
He asked me to determine each employee’s variance and efficiency level percentage.
Here, we first need to find the variance, and the formula is Actual – Target. For efficiency, the formula is Actual / Target, and for Variance %, the formula is Efficiency % – 1.
Calculate Variance Amount
The variance amount is calculated by subtracting the actual from the target number.
We must drag the formula to cell D10 to determine the other values.
Calculate Efficiency Percentage
Efficiency is calculated by dividing the actual by the target. (if the result shows in decimals, apply percentage formatting).
We need to drag the formula to cell E10 to determine the other values.
Calculate Variance Percentage
Variance percentage is calculated by deducting 1 from an efficiency percentage.
We need to drag the formula to cell F10 to determine the other values.
Things to Remember
- We must apply percentage formatting in case of decimal results.
- We can either directly insert the numbers or give cell referenceGive Cell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1..
- Giving cell reference makes the formula dynamic.
- One must always remember the basic Mathematics rules.
- One must try to learn the BODMAS rule in Mathematics.
This article has been a guide to Excel Minus Formula. Here we discuss using the minus formula in Excel with examples and downloadable Excel templates. You may also look at these useful functions in Excel: –