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