Do you want to calculate the percentage change in your Google Sheets but can’t find a specific function to do so? Don’t worry, I’ve got you covered! In this article, I will show you how to write an array formula using arithmetic operators to calculate the percentage change in Google Sheets. So let’s dive in!
Change and % Change
Before we start, let’s understand the concept of change and percentage change. Change is simply the difference between two values, usually a new value and an old value. On the other hand, percentage change represents this difference in percentage.
For example, let’s say according to my Google Analytics statistics, the traffic to my website was 7720 visitors yesterday and 7304 visitors on the same day last week. The change in site traffic can be calculated as follows:
=7720-7304
Result: 416
The percentage change in site traffic can be calculated using the following formula:
=416/7304
Result: 0.57, which is equivalent to 5.70%
Now, let me explain how to calculate the percentage change in Google Sheets.
Calculating Percentage Change in Google Sheets
Non-Array Formula
To calculate the percentage change in Google Sheets, you can use the following formula:
=to_percent((C2-C3)/C3)
The formula (C2-C3)/C3 calculates the percentage change. However, this formula would return the result as 0.0569550931, which needs to be formatted as a percentage. That’s why I have included the to_percent function in the formula.
Alternatively, you can rewrite the formula using equivalent functions to the arithmetic operators, like this:
=to_percent(divide(MINUS(C2,C3),C3))
This formula will give you the same result in a formatted percentage.
Array Formula to Calculate Percentage Change of Values in a Column in Google Sheets
If you want to calculate the monthly percentage changes from January to December in a column in Google Sheets, you can use an array formula. Let’s take a look!
In a column, let’s say we have month-wise (Jan to Dec) sales amounts. To calculate the monthly percentage changes, you can use the following non-array formula:
=TO_PERCENT(iferror((B2-B1)/B1,0))
Make sure to leave cell C1 empty because the first row contains headers. Then, use this formula in cell C2 and drag it down.
This formula will give you the percentage change for each month. But what if you have a large set of data in many rows? In that case, you can use the following array formula to calculate the percentage change in Google Sheets:
=ArrayFormula(if(B2:B="",,IFERROR((B2:B-B1:B)/B1:B,0)))
Here are a few points to keep in mind when using this array formula:
- Do not copy down the formula.
- If you see a #REF! error, make sure that column C (C3:C) is blank.
- Format the array C2:C to percent using the « Format > Number > Percent » option.
- Make sure there are no blank cells in the data range (B2:B). In other words, there should be no blank cells between B2 and B13, for example.
That’s all there is to it! Now you can easily calculate percentage changes in Google Sheets using these formulas.
Conclusion
Calculating the percentage change in Google Sheets might seem daunting at first, but with these array formulas and a little practice, you’ll become a pro in no time. So go ahead, give it a try, and let me know how it goes!
If you want to learn more about Google Sheets and discover other useful tips and tricks, make sure to visit Crawlan.com. Happy calculating!
–Author’s Name
