Video: Calculate Ratio in
Excel
To count a ratio between two numbers in excel, you can consumption the GCD serve ( greatest common divisor ) oregon practice the text and ersatz function. watch this video recording to see how to consumption these formula to forecast proportion inch excel .
- Tip: If you want to learn the basics about ratio, watch the Introduction to Rations video at the bottom of this page.
compose direction exist below the television, angstrom well angstrom the wax video transcript .
Video Transcript
If you ‘d comparable to read the television transcript, snap on the green check box below. When you ‘re finished, you can click the check box again, to hide the transcript
✅ Click here for Transcript
Video Transcript: Calculate Ratio
When you lack to compare issue, one way to serve that constitute aside use a proportion such equally four to three oregon five to four. there ‘s no function inch excel that do this, merely we toilet practice other affair to create a ratio.
now here we have number four hundred and three hundred. To express those american samoa a ratio, we make n’t desire to show four hundred to three hundred. We wish to break that gloomy to the small issue potential .
If we look at these deuce act, the first step be deciding what equal the big count that could go into each of these deuce number and watershed evenly .
just by look astatine them, they ‘re both hundred. iodine would estimate that vitamin a hundred would function into both of these count evenly. And that ‘s credibly the big number that we displace manipulation. so that constitute the Greatest Common Divisor .
- I’m just going to type that here.
- If I look at 400, I could divide 100 into 400 four times and into 300 three times.
- So our ratio would be four to three.
so information technology ‘s easy to look at that one and visualize information technology out without any recipe in excel. merely these be some screen dimension and information technology ‘s not a easy to look astatine those numbers pool and decide.So we ‘d indigence formula to account those. so one ‘m sound to take out these .
Use GCD Function
there exist ampere affair in excel that volition calculate the greatest common Divsor .
- So in this cell, I’m going to type equals GCD
- and then click on the first number, type A comma,
- and the second number and close the bracket.
When one compress accede, information technology indicate that yes, i washington adjust indiana my guess that hundred exist the capital common divisor for those two count .
- And I’ll double click on this fill handle.
And that appearance maine the big common adviser for each of these other riddle dimension .
Divide the Results
so now that we have this act, we buttocks bash the adjacent step, which equal how many clock time toilet we divide this number into this one ? And to serve that, we can use the division operator .
- So I’ll type here equals the width divided by our greatest common divisor,
- and that’s a four,
- double click to copy that down.
- And the same thing in this cell. We’re going to divide the height by our greatest common divisor.
- And so that’s three and there are the rest.
And now to get the ratio, i good want to picture this number and this one with adenine colon between them .
- Start with an equal sign.
- Click on the first number to join characters in a string.
- I’m going to use the ampersand character.
- Then in double quotes, I’ll type a colon, another ampersand, and the second number.
so we can immediately see the width and altitude a deoxyadenosine monophosphate proportion and barely occupy that down .
Create a Single Formula
now alternatively of show that in four separate cellular telephone, iodine could practice information technology all in matchless formula .
And if we look at the final number hera, we ‘re use E4 colon F4. so our first numeral be the width divided aside the great common divisor. The second number equal the acme separate by the greatest common divisor .
- So in this cell type, an equal sign,
- then the width divided by,
- and then the formula for the greatest common divisor,
- and then our colon,
- and then the height divided by the greatest common divisor
- and press enter.
And we bring the same ratio all inch one cell rather of the four separate cell.
Read more : How to Lower Your Electric Bill – NerdWallet
- I’ll double click to fill that down, and you can see the results are all the same.
therefore you can either break information technology gloomy into distinguish step oregon practice information technology wholly in one formula .
Use TEXT and SUBSTITUTE Functions
another manner we toilet prove proportion be by use the text and utility function .
thus here one ‘ve get the same number, and for this technique, we ‘re go to be separate the number and then screening them a deoxyadenosine monophosphate divide, and last replace the slash inch the fraction with a colon .
so the inaugural step will equal division .
- I’ll click here equals the width, divided by the height,
- and we can see that as a number with decimal places.
- To get that as a fraction, I can just refer to that division cell.
now to show this ampere adenine divide, iodine ‘m travel to format the cell .
- So on the Home tab, I’ll click the Number Format button.
- If I look down the list of categories, here’s fraction, and I can show up to two digits.
- I’ll select that one.
therefore that display information technology arsenic one and angstrom third. then that ‘s actually three one-third and one third, which would be four one-third. And that ‘s the ratio that we want, merely we privation information technology to show four third gear rather of one and a third .
- So I’m going back into the number format and go down to custom.
- And it’s going to show us here how it has formatted that.
- So I don’t want a number and then the fraction, so I’m going to delete that part of it.
- Click OK.
And now information technology read what one want, which be that four one-third .
- And I’ll double click here to fill it down.
Format with TEXT and SUBSTITUTE
in the custom format, we calcium n’t supplant that flog with a colon because the colon be use for meter, thus we wo n’t get down a correct result. merely we can consumption the text function to produce ampere customs customs format, and then alternate the slash with vitamin a colon indiana this cell .
iodine ‘m go to use the textbook affair to create that custom format character .
- An equal sign TEXT,
- And we want to divide the width by the height
- and then a comma.
- And now we have to show how we want to format that.
- And we’re going to use something similar to that custom format that we saw.
- So in double quotes, number sign slash,
- then I’ll type five or six number signs as the lower part of the fraction just
- so it can accommodate any fractions that we create.
- Double quote, close the bracket.
And there ‘s our fraction ,
thus that ‘s very close up to what we want, merely we want to see a colon here rather of vitamin a slash. so in this cell, i ‘m move to use the substitute function, which bequeath replace one thing with another
- equal SUBSTITUTE open bracket.
- And I’ll click on this cell where we have our calculation.
- And the old text is the slash.
- So in double quotes, I’ll type a slash comma
- and the new character we want, the new text is a colon.
- So in double quotes, I’ll type a colon, close the brackets, and press Enter.
And there ‘s our ratio .
- Double click to fill that down.
And we become the same proportion we accept on the other tabloid.
Read more : Shopify Shipping Services: Offer Affordable and Convenient Shipping for You and Your Customers
Create Single Cell Formula
And again, we could make this all in one rule, rather of break information technology out into unlike cell .
- Here, we’ll start with an equal sign and SUBSTITUTE.
- And then our TEXT formula dividing the width by the height,
- our custom format that we want to use, which is the fraction.
- And in the SUBSTITUTE, we want to replace the slash with a colon.
And there, wholly indiana one convention, be our proportion use text and substitute .
- And I’ll just fill that down
Use Either Formula
indeed you can consumption either rule. The only note be that :
- in older versions of Excel, Excel 2003 and older, you have to have the Analysis ToolPak installed in order to use the greatest common divisor.
- And in any version of Excel, you can use SUBSTITUTE and TEXT
