Minus Formula in Excel
In excel we do not have any inbuilt formula for subtraction or minus, we use minus operator (-) to do the arithmetical subtraction in excel, to subtract two values from each other we need to also use the equals to 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 error in formula.
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. It is categorized under the math and trigonometry function entered by typing “=SUM” followed by the values to be summed. The values 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. I want to subtract the value in B1 from A1. I want the result to be shown in cell C1. The formula in the cell C1 should read like this =A1 – B1.
We can also directly enter the values to the cell C1 itself.
Even though we can directly enter the numbers to 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
I have data on Budgeted Cost vs. Actual Cost for the year 2018. The finance department supplied me with these two data. I need to find out the variance amount whether the budgeted cost is within the limit or not?
How do I find out the variance amount? I need to deduct the budgeted cost from the actual cost.
- I need to deduct B2 from A2 using the minus formula.
- Press enter 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., C6 cell. If the variance is negative, then it is over the budgeted number, and if the variance is positive, then it is within the budgeted number.
We know the basics of minus calculation. In this example, we will look at how to deal with one negative number and one positive number.
I have data on Quarterly profit and loss numbers.
I need to find the variance from Q1 to Q2, Q3 to Q4, Q5 to Q6.
The usual formula should be Variance = Q1 – Q2, Variance = Q3 – Q4, Variance = Q5 – Q6.
Now look at the first variance in the Q1 loss was -150000 in the second Q2 profit is 300000, and the overall variance should be a profit of 150000. But formula displays -450000. This 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. Since there are a positive number and negative numbers, we need to include the plus sing here. The basic of mathematics is plus * minus = minus.
Now look at the third variance in the Q5 loss was -75000 and in Q6 loss was -125000. 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, minus sign, and the second number. 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 need to do the subtraction operations in excel. You should know the basics of mathematics to deal with these kinds of situations.
Below is the data are given to me by the sales manager. This data is the individual sales data for his team. Data includes individual target and individual actual sales contribution.
He asked me to find out the variance and efficiency level percentage for each employee.
Here first things I 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 Actual from the Target number.
Now drag the formula to cell D10 for the other values to be determined,
Calculate Efficiency Percentage
Efficiency is calculated by dividing the Actual by Target. (if the result shows in decimals, apply percentage formatting)
Now drag the formula to cell E10 for the other values to be determined,
Calculate Variance Percentage
Variance percentage is calculated by deducting 1 from an efficiency percentage.
Now drag the formula to cell F10 for the other values to be determined,
Things to Remember
- Apply percentage formatting in case of decimal results.
- We can either directly enter 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.
- Always keep in mind the basic mathematics rules.
- Try to learn the BODMAS rule in mathematics.
This has been a guide to Excel Minus Formula. Here we discuss how to use the minus formula in excel with examples and downloadable excel templates. You may also look at these useful functions in excel –