Excel Minus Formula

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?

In Excel for adding two numbers, we have 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. 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

You can download this Minus Formula Excel Template here – Minus Formula Excel Template

Example #1

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.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Example #2

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.

Case 1:

Now look at the first variance in the Q1 loss was -150000 in the second Q2 profit is 300000 and 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 number we need to include plus sing here. The basic of mathematics is plus * minus = minus.

Case 2:

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 excel the Q6 value from 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.

Example #3

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