[ad_1]
Calculating the percentage is one of those things that makes many people cringe. But if displaying a percentage in your spreadsheet is beneficial, we’ll show you some easy ways to calculate a percentage and format it in Google Sheets.
Method one: split part of a whole
You can calculate the percentage of part of a whole with a simple formula in Google Sheets.
The syntax of the formula is =(part/total) or part divided by the whole. You can use cell references or inserted values ​​for the part and total, or a combination of the two.
To find the percentage of the part in cell A1 of the total in cell B1, you would use the following formula:
=A1/B1
Here the result is 0.25 or 25 percent.
To find the percentage of a part in cell A1 to the total of 100, you would use this formula:
=A1/100
To find the percentage of the 25th part of the whole 100, you would use the following:
=25/100
You’ll notice that when Google Sheets gives you the percentage, it’s formatted as a decimal number by default. But you can change this automatically or manually to percentage if you want.
Automatically format as percentage
To format the result as a percentage automatically when you calculate it, you can add a function. The benefit is that you can not only calculate and format the result at the same time, but also adjust the decimal places. To do this, use the TO_PERCENT function.
RELATED: How to change and create a custom number format in Google Sheets
The syntax of the function is TO_PERCENT(value) where you can use a cell reference or a number for the required argument.
Using our first percentage formula above, you can use this formula to convert the result:
=TO_PERCENT(A1/B1)
The same goes for the other two formulas we used to calculate percentages above:
=TO_PERCENT(A1/100)
=TO_PERCENT(25/100)
Manually format as percentage
To manually format the result of your formula as a percentage, select the cell that contains the decimal and do one of the following:
- Click the Format as Percentage button on the toolbar.
- Choose Format > Number > Percentage from the menu.
- Click the More Formats button on the toolbar and select “Percentage.”
Advice: You can also use the Increase Decimal Places or Decrease Decimal Places button on the toolbar to arrive at a whole number for your percentage.
Method Two: Divide Part of a Whole and Multiply
As an alternative to the first method, you can eliminate the decimal result when calculating the percentage by multiplying the division by 100.
RELATED: How to multiply numbers in google sheets
This syntax is =(part/total)*100 or part divided by the total by 100. You can include or remove the parentheses in the formulas below and get the same results.
Using the first example above, you would use this formula.
=(A1/B1)*100
Now you receive the result 25 instead of 0.25.
The same goes for the other formulas:
=(A1/100)*100
=(25/100)*100
Method Three: Calculate Percentage Based on Criteria
While the above options are great for simple percentages in Google Sheets, you may need something more robust, like calculating a percentage based on certain conditions. In this case, you can use the PERCENTIF function.
RELATED: How to Calculate Percentage Increases in Excel
The syntax of the function is PERCENTIF(range, criteria) where he criteria can be a number, text, or cell reference. The function also formats the result as a percentage automatically.
To find the percentage of bills that are over $50 in cell range B2 through B7, you would use this formula:
=PERCENTIF(B2:B7,">50")
For an example of text criteria, you can use this formula to calculate the percentage of our expenses in cells A2 through A7 that are for fuel.
=PERCENTIF(A2:A7,"Fuel")
Since the function also allows wildcards, let’s look at one more example. Using this formula, you can calculate the percentage of students whose names begin with the letter J in the range D2 to D7.
=PERCENTIF(D2:D7,"J*")
Calculating percentages in Google Sheets might be something you plan on doing with your next sheet. So keep this tutorial in mind for a little help with those calculations.
[ad_2]
