In this article:

May 8, 2024

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.

Follow the steps below to calculate ratios in Google Sheets.

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.

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

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.

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.

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

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

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 ->