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, 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

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 in negative, then it is over the budgeted number and if the variance is positive then it is within the budgeted number.

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.

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

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 decimal results.
• We can either directly enter the numbers or give cell reference.
• Giving cell reference makes the formula dynamic.
• Always keep in mind the basic mathematics rules.
• Try to learn BODMAS rule in mathematics.

Recommended Articles

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 –

• Use Formula in Excel as a Calculator
• How to Subtract Time in Excel?
• Quotient in Excel Examples
• How to Multiply in Excel Formula?
• Formula for Divide in Excel