How to use Excel functions to show a ratio, such as 4:3 or 8:5. To calculate ratio in Excel, you can use the GCD function, or the TEXT and SUBSTITUTE functions.
To calculate a ratio between 2 numbers in Excel, you can use the GCD function (Greatest Common Divisor) or use the TEXT and SUBSTITUTE functions. Watch this video to see how to use these formulas to calculate the ratios in Excel.
Written instructions are below the video.
In this example, there is a list of screen dimensions, with the first width -- 400 -- in cell B4 and the first height -- 300 --in cell C4.
How can you calculate a ratio in Excel, to see those screen dimension the way they're in an advertisement for computer monitors? Or the rations that are the options when you're cropping photos in your photo editing software?
For example, you might crop a photo to a 4:3 ratio, or save a video in 16:9 ratio.
To calculate the ratio for each screen, the formula will use the GCD function, which returns the Greatest Common Divisor, for two or more integers.
The result of the GCD function is the largest integer that divides both numbers, without leaving a remainder.
NOTE: In Excel 2003 and earlier, the Analysis Toolpak must be installed, to use the GCD function.
The GCD function has the following syntax for its arguments:
GCD(number1, [number2],...)
Notes:
If any of the numbers are not integers, those numbers will be truncated to zero decimal places.
All numbers must be zero or above, or the result will be a #NUM! error
In this example, the first number, 400, is in cell B4. The second number, 300, is in cell C4.
To see the largest number which will divide equally into the numbers in B4 and C4, enter this formula in cell D4:
The result is 100. That is the largest integer that divides both numbers (300 and 400), without leaving a remainder
To calculate the ratio, the width will be divided by the GCD and the height will be divided by the GCD. A colon will be placed between those two numbers.
To see the ratio, enter this formula in cell E4:
=B4/GCD(B4,C4)&":"&C4/GCD(B4,C4)
The result is 4:3 -- the ratio for those screen dimensions.
Another way to calculate ratio is with the TEXT and SUBSTITUTE functions -- these functions work in all versions of Excel, without the Analysis Tookpak having to be installed.
In this example, there is a list of screen dimensions, with the first width -- 400 -- in cell B4 and the first height -- 300 --in cell C4.
To calculate the ratio for each screen, the formula will divide the width by the height, and format the result as a fraction. Then, the slash will be replaced with a colon, to create the ratio.
To see the result as a fraction, enter this formula in cell D4:
=TEXT(B4/C4,"#/######")
The result is 4/3.
To calculate the ratio, the width will be divided by the height, and formatted as a fraction. A colon will replace the slash.
To see the ratio, enter this formula in cell E4:
=SUBSTITUTE(TEXT(B4/C4,"#/######"),"/",":")
The result is 4:3 -- the ratio for those screen dimensions.
Download the zipped sample Excel Ratio Formulas file
INDEX function and MATCH Function
Last updated: May 19, 2022 7:21 PM