Excel Minus Formula

Publication Date :

27 Jun, 2019

Blog Author :

Edited by :

Reviewed by :

Download FREE Minus Formula In Excel Template and Follow Along!

Minus Formula Excel Template.xlsx

Table Of Contents

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.

How to Use Minus Formula in Excel?

For adding two numbers, we have the SUM function in excel.

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

Example #1

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.

Example #2

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.

Case 1:

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.

Case 2:

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 excel 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.

Example #3

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