Do you ever use Excel to calculate a ratio? In business, you could compare income to expenses, and at home, you could use ratios to bake a cake, without a recipe. In my latest video, I show a quick Excel formula for the ratio between two numbers, in x:y format. And no, Excel doesn’t have a RATIO function, even though one overly-confident AI helper tool said it does!
Video: Calculate Ratio – 3 Steps
In this 2-minute video, see how to create a ratio formula in Excel. The result shows the ratio between two numbers, in x:y format.
The formula is created in 3 steps, using division first, then the formatting that result with the TEXT function, and finally, adding the SUBSTITUTE function.
Video Timeline:
- 00:00 Introduction
- 00:23 Step 1 – Divide
- 00:37 Step 2 – Fraction
- 01:17 Step 3 – Ratio
NOTE: There’s a longer Ratio video at the end of this page, with a GCD function example too.
Finding Help for Excel Ratio Formula
Yesterday, just for fun, I tried to find help on how to write an Excel ratio formula, posing as someone who had no idea how to do that.
First, I used Excel’s built-in help, and it didn’t look too helpful. When I searched for “ratio formula”, there were five links in the results, and none of them mentioned ratio. The closest was the fifth link, Calculate percentages.
So I clicked that link, and the tutorial didn’t mention ratio, but it did show how to calculate percentage by dividing.
Help Me Obi Wan Keno-bot
There are a few Excel formula helper bots now, so I tried one of those next. (I’ll leave the tool’s name out of this story.)
To use this formula helper, I had to sign up, using my email, but there is a free plan, and that gives you a few uses per month.
First, you enter a text description of the problem that you’re trying to solve, and then the AI tool suggests a formula.
1) This was my first prompt, asking for help:
- Get the ratio between numbers in cells B4 and C4 in x:y format
And here was the suggested formula:
- =CONCATENATE(B4,”:”,C4)
Well, that was technically correct, I guess. But…we usually want the smallest numbers possible, in the ratio results.
2)Â Next, I entered another prompt, with more details on the result that I wanted:
- Get the ratio between numbers in cells B4 and C4 in x:y format, reduced to lowest terms
And here’s the suggested formula for that:
- =TEXT(B4/C4,”x:y”)
Things were getting worse!
3) And here’s my final prompt, in case the bot was confused by my “x:y” format request:
- calculate the ratio between numbers in cells B4 and C4
And here’s the formula – a simple division:
- =B4/C4
Another Bot, More Disappointment
Next, I tried one of the AI writing tools, which shall also remain nameless.
Here was my prompt:
- Please write 4 formula examples to calculate a ratio in Excel, showing the ratio in a:b format
In the screen shot below, you can see the 4 formulas that it suggested, and the results of those formulas.
- =A1:B1
- =CONCATENATE(A1, “:”, B1)
- =TEXT(A1/B1, “0:#”) & “:1”
- =SUBSTITUTE(TEXT(A1/B1,”0.0″), “.”, “:”) & “:1”
The last two suggestions were better than the formula helper’s, but they used the wrong format in the TEXT function.
For this solution, TEXT needs a fraction format, like “#/####”, instead of a colon (which creates a time), or a period.
- Tip: The fraction format does some of the heavy lifting – it reduces the result to the lowest terms
Imaginary Excel Function
After that experiment failed, I made one final attempt to get help from the writing AI helper, and used the following prompt:
- Please define what a ratio is and how it’s calculated in Excel
The response started sensibly, with a good definition of ratio, and a simple division formula.
But then, in a shocking twist, the bot invented a new Excel function to solve my problem. Here’s the final paragraph:
- Excel also has a built-in function for calculating ratios called “RATIO”.
- This function takes two arguments, the numerator and denominator of the ratio, and returns the result as a decimal value.
- For example, the formula “=RATIO(A1,B1)” will calculate the ratio of A1 to B1.
And my question is, if we did have that Excel function, wouldn’t it just be named DIVIDE?
Video: Two Formulas for Excel Ratio
Way back in 2014, I added a page on my Contextures site, with two different formulas for calculating ratios in Excel, and showing the results in x:y format.
My original video show both methods – using the GCD function, and using TEXT with SUBSTITUTE. It’s a thorough explanation, and almost 9 minutes long!
That’s why I made the new video – our attention spans are shorter now!
Video Timeline
- 0:16 Ratio introduction
- 1:20 GCD formula
- 4:03 TEXT and SUBSTITUTE formula
Get Ratio Formulas and Sample File
Anyway, it was fun to try the helper tools, and I’m sure they’ll get better over the coming months.
For now, and as always, you can find Excel help on my Contextures site, which is packed with tutorials, videos, and sample files.
To see my Excel ratio formulas, and to get the sample files, go to the Calculate Ratio in Excel page. In the download section, you’ll find the two Excel files that I used in the Ratio formula videos shown above.
________________
Calculate Ratio with Excel Formula – 3 Steps
________________