In this article:

How to Calculate Ratio in Google Sheets (Easiest Way in 2024)

May 8, 2024

Ratio Formula in Google Sheets

In Google Sheets, to calculate the ratio of two numbers, you don't have a built-in "ratio" function, but you can create a formula to express one number as a ratio of another. 

Essentially, you are looking for the simplest form of expressing one value compared to another, often in terms of integers. One straightforward approach is to use the GCD function (which finds the greatest common divisor of two numbers) to simplify the ratio.

Here's how you can do it:

Assuming you have two numbers in cells A1 and B1, you can use the following formula to get their ratio in the simplest form:

=A1 / GCD(A1, B1) & ":" & B1 / GCD(A1, B1)

Here's a breakdown:

  • A1 and B1 are the cells containing the two numbers you want to find the ratio of.
  • GCD(A1, B1) calculates the Greatest Common Divisor of the two numbers, which is used to simplify the ratio.
  • A1 / GCD(A1, B1) calculates the simplified value of the first number in the ratio.
  • B1 / GCD(A1, B1) calculates the simplified value of the second number in the ratio.
  • & ":" & combines the two simplified numbers with a colon (:) in between, presenting the result as a ratio.

This formula divides each number by their greatest common divisor (GCD), which simplifies the ratio to its smallest possible integer values, and then concatenates the results with a colon ":" to represent the ratio.

How to Calculate Ratio in Google Sheets

Follow the steps below to calculate ratios in Google Sheets. 

1. Input Your Numbers in Separate Cells

Start by entering the two numbers you want to find the ratio of into two separate cells. For instance, place the first number in cell A1 and the second number in B1.

how to calculate ratio in google sheets

2. Select the Cell for Displaying the Ratio

Click on the cell where you wish the calculated ratio to appear. This is where the formula for calculating the ratio will be entered.

ratio in google sheets

3. Type the Ratio Formula into the Selected Cell

In the selected cell, type in the formula =A1 / GCD(A1, B1) & ":" & B1 / GCD(A1, B1). This formula uses the numbers in cells A1 and B1 to compute their simplified ratio.

google sheets ratio

4. Press Enter to Calculate and Display the Ratio

Press Enter after you've entered the formula. Google Sheets will now calculate the ratio and display it in the chosen cell, showing the simplified ratio of the two numbers.

5. Adjust Formula for Different Numbers as Needed

To calculate the ratio for different numbers, either adjust the numbers in cells A1 and B1 or modify the cell references in the formula accordingly.

We hope that you now have a better understanding of how to calculate ratios in Google Sheets. If you enjoyed this article, you might also like our article on how to change Google Sheets to landscape or our article on how to set up Google Sheets non-contiguous range.

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->