Annyca Tabassum
In Google Sheets, you can subtract thousands of data at a time by applying functions and formulas. Though there is no specific function for Subtraction, you can apply the subtract sign with different functions to create complex formulas. In this article, we will learn how to subtract a percentage in Google Sheets.
A Sample of Practice Spreadsheet
You may copy the spreadsheet below and practice by yourself.
4 Methods to Subtract a Percentage in Google Sheets
1. Inserting Percentage Formula
You can use the subtract sign to calculate different mathematical issues. Here, we will show the subtraction with the percentage step by step.
Step 1: Create Dataset
To show the subtraction with the percentage we develop a dataset that contains the sale information of some products.
- We create a dataset with three columns: Product Name, Current Price, and Sale Price. Here, we have to find out the sale price of the products.
- We also develop a cell that contains the Sale (%) of the products.
Read More: How to Calculate Percentage in Google Sheets (4 Ideal Examples)
Step 2: Subtract the Percentage Amount
To know the sale price of the products we have to use the subtract formula with percentage. To do so,
- First select cell F5 and insert the sale percentage. Here, we add 30% as a sale percentage.
- This is the amount in percentage that we will look to subtract from the Current Price.
- Then select cell D5 to insert the subtract formula.
- Now, input “=” sign and reference cell C5.
- Then, apply the subtraction operator which is simply the ‘–’ sign on your keyboard.
- After that add a first bracket into the formula.
- Again, insert cell C5 which represents the current price of the product TV.
- Now, to calculate the sale price multiply the cell C5 by cell F5. Here, the C5 cell is the current price of a specific product and the F5 cell represents the sale percentage of that product. The calculation is:
C5*F5= $430*30% OR $430*0.30= $129
- $129 is the amount to be subtracted from the Current Price.
Read More: How to Calculate Percentage Change in Google Sheets (4 Ways)
Step 3: Final Output
To get the final output,
- Close the parentheses on the formula and press ENTER. You will get the Sale Price of the specific product.
=C5-(C5*F5)
- To apply the formula to the entire column, drag the fill handle downward. Be sure to lock (italics) the Sale (bold) value cell reference with absolutes ($):
=C5-(C5*$F$5)- Here, $F$5 represents constant cell F5 for the whole column.
Read More: Google Sheets Calculated Field Percentage of Total in Pivot Table
2. Using Generic Formula
You can use the following formula to subtract with percentage as well.
=C5*(1-$F$5)Here, C5 is the value from where subtraction is done and $F$5 represents the absolute cell reference where the value is 30% or 0.30. So, the calculation is,
C5*(1-$F$5)=$430*(1-30%) OR $430*(1-0.30)=$301
Read More: How to Make a Percentage Chart in Google Sheets (3 Ways)
3. Applying MINUS Function
You can apply the MINUS function to get the subtraction of the percentage value. To do so,
- First, select cell D5 and insert the MINUS function.
- Now add cell C5 as value1.
- After that input C5*F5 where F5 represents the sale percentage of 30%.
- In the end press ENTER and you will find the desired value in the selected cell.
=MINUS(C5,C5*F5)
- Finally, by dragging down the Fill handle you can apply the function to the entire column. For the entire column, we have to add ‘$‘ sign to make the F5 cell as absolute reference.
=MINUS(C5,C5*$F$5)
Read More: How to Calculate Percentage Increase in Google Sheets (4 Ways)
4. Inserting ARRAYFORMULA Function
You can input the ARRAYFORMULA function with any of these methods described at the top to get a range of data. So, for the ARRAYFORMULA function,
- First select cell D5 and insert the ARRAYFORMULA function.
- Then input the C5:C10 range as a value.
- After that add subtract sign and then C5:C10*F5. Here F5 is the sale percentage.
- You do not need to use absolutes ($) on F5 this time since we are not using a fill handle.
- Finally, click on ENTER and you will find the values in the entire column in one go.
=ARRAYFORMULA(C5:C10-C5:C10*F5)
Read More: How to Use TO_PERCENT Function in Google Sheets
Things to Remember
- Insert the cell carefully in the formula.
- Always use the ‘$’ sign before the cell to make it constant.
Conclusion
We believe from this article you can clearly get how to subtract a percentage in Google Sheets. Moreover, to learn more about subtraction in Google Sheets you can visit the OfficeWheel website.
