How to Calculate Percentage Difference In Google Sheets

Published on

When comparing two values in Google Sheets—like site visits or someone’s height—it’s not always about which one came first. That’s where percentage difference comes in. Unlike percentage change, it treats both values equally. In this post, I’ll show you how to calculate the percentage difference in Google Sheets with a simple, accurate formula. There’s no built-in function in Sheets specifically for this.

Before we go into the formula, it’s important to understand the difference between percentage difference and percentage change.

We typically use percentage change when comparing an old value (like last month’s production) with a new value (this month’s production).

But percentage difference is a bit different — it gives equal weight to both values and is best when neither value is a clear “starting” point.

A Simple Example

Assume I published two Google Sheets tutorials on 01-10-2021.

Here’s the site traffic (number of visitors) to those two tutorials:

  • Tutorial 1: 500
  • Tutorial 2: 450

To manually calculate the percentage difference, use this formula:

Formula syntax:

=(absolute difference of the two values) / (average of the two values)

Since we’re interested in the absolute difference, we ignore the sign of the result.

Example calculation:

=(500 - 450) / ((500 + 450) / 2)

Result: 0.1053, which equals 10.53%

Formula to Calculate Percentage Difference in Google Sheets

Let’s put those two values in cells A1 and A2. Then, in cell B1, enter:

=TO_PERCENT(
   DIVIDE(
      ABS(MINUS(A1, A2)), AVERAGE(A1:A2)
   )
)

This formula uses:

  • MINUS(A1, A2) to find the difference
  • ABS(...) to get the absolute value
  • AVERAGE(...) to calculate the mean
  • DIVIDE(...) to divide the absolute difference by the average
  • TO_PERCENT(...) to format the result as a percentage

You can also simplify the formula using arithmetic operators:

=TO_PERCENT(ABS(A1 - A2) / AVERAGE(A1:A2))

Another Example: Comparing Heights

Let’s say we’re comparing the heights of two goalkeepers:

  • Goalkeeper 1: 6.3 ft
  • Goalkeeper 2: 6.5 ft

Enter 6.3 in B1 and 6.5 in B2. Then use the following formula in B3:

=TO_PERCENT(ABS(B1 - B2) / AVERAGE(B1:B2))
Percentage difference of height between two people in Google Sheets

The result will be 3.13%, which is the percentage difference in their height.

Note: Whether you input height in feet, centimeters, or inches, the result will remain the same, since we’re comparing relative difference.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.