Google Sheets: Calculate Percentage of Another Cell
In Google Sheets, you might often find the need to calculate the percentage of another cell's value relative to a total or a comparison value. While it may seem complex, Google Sheets offers a simple and effective method to achieve this using its built-in functions. In this guide, we will explore formulas to calculate the percentage of another cell's value in Google Sheets.
Understanding the core concept is crucial before diving into the formulas. You might want to express a part (cell A) as a percentage of the whole (cell B), or compare two values (cell C and D) as a percentage of each other. We'll cover both scenarios in this article.
Calculating Percentage of a Total (Cell B)
To find the percentage of a cell (A) relative to another cell (B), you can use the following formula:
(A2/B2)*100
Here, A2 is the value whose percentage we want to find, and B2 is the total or base value. Replace '2' with your respective cell reference.
For example, if A2 has 50 and B2 has 100, the formula will calculate 50% (as 50 is 50% of 100).
Using named ranges for clarity
When working with large datasets, named ranges can improve readability and maintainability. Here's how to name ranges 'TotalSales' and 'GoalSales':
- Select the cell (B2 in our example),
- Click 'Insert' in the menu, then 'Named range' and 'Create' to confirm.
Now, the formula becomes (A2/TotalSales)*100.
Auto-filling the formula
If you want to replicate this calculation for multiple rows, you can drag the formula in the fill handle (small square at the bottom right corner of the cell) or use array formula (=ARRAYFORMULA((A2:A/TotalSales))*100).
Calculating Percentage Change Between Two Cells (C and D)
To find the percentage change between two values (C and D), use the following formula:
((D2-C2)/C2)*100
Here, D2 is the new value, and C2 is the original/previous value.
For instance, if C2 has 100 and D2 has 120, the formula will calculate +20% (as 120 is 120% of 100, and the change is +20%).
Using the PERCENTCHANGE function
For a more straightforward approach, use Google Sheets' built-in PERCENTCHANGE function: =PERCENTCHANGE(B2,C2). Here, B2 is the new value, and C2 is the old value.
Google Sheets offers powerful and intuitive tools to work with percentages. By mastering these formulas and functions, you can simplify your data analysis and reporting. Happy calculating!