A ratio is a mathematical term that shows the comparison between two or more numbers. It is typically expressed in the form a/b or a:b. In this article, we will delve into four techniques for computing ratios using Excel functions.

Table of Contents

• Technique 1 – Basic Division
• Technique 2 – GCD Function
• Technique 3 – Custom Formatting
• Technique 4 – TEXT & SUBSTITUTE Functions

Let’s examine each of these techniques thoroughly.

You can download the Excel Workbook below to follow along and learn How to Compute Ratios in Excel –

download excel workbookCalculate-Ratio-in-Excel.xlsx

Technique 1 – Basic Division

This technique works well when the larger number can be evenly divided by the smaller number. For instance, if you have two values, 1200 and 400, and you wish to present them as a ratio i.e. a:b.

You can initially divide the two values using the division (“/”) symbol and then use the & symbol to insert the colon. Then, you can append 1 after the colon.

The formula for this approach is:

=A2/B2&”:”&1

Follow these steps to use basic division to compute a ratio:

STEP 1: Divide the two values in cell C2.

=A2/B2

STEP 2: Add the & symbol.

=A2/B2&

STEP 3: Add the colon symbol within quotes.

=A2/B2&”:”

STEP 4: Add the & symbol.

=A2/B2&”:”&

STEP 5: Enter 1.

=A2/B2&”:”&1

The outcome will be 3:1.

This method is less effective if the numbers do not divide evenly and result in a remainder. If you divide 1200 by 500, the result will appear in decimal format as shown below.

For such cases, you can refer to the other techniques discussed in this article.

Technique 2 – GCD Function

The GCD function returns the greatest common divisor of two or more integers, which is the largest integer that divides both numbers without a remainder. The syntax for this function is:

=GCD(number1, [number2], …)

where,

• number1 – The first number. Required.
• number2 – The second number. Optional.

Suppose you have data on the production output of different factories in terms of units produced and the number of defective units, and you want to calculate the ratio of good units to defective units.

The goal is to find the GCD between these two integers and then use it to compute the ratio. Follow these steps to compute ratios in Excel:

STEP 1: Enter the GCD formula in cell D2.

=GCD

STEP 2: Select the first number in cell B2.

=GCD(B2,

STEP 3: Select the second number in cell C2.

=GCD(B2,C2)

STEP 4: Press Ctrl D to copy the formula down.

This will give you the largest integer that divides both numbers without leaving a remainder.

STEP 4: Divide the first number by the GCD (calculated in cell D2).

=B2/D2

STEP 5: Add the & symbol.

=B2/D2&

STEP 6: Add the colon symbol within quotes.

=B2/D2&”:”

STEP 7: Add the & symbol.

=B2/D2&”:”&

STEP 8: Divide the second number by the GCD (calculated in cell D2).

=B2/D2&”:”&C2/D2

The ratios will be shown in column E. This is a quick and straightforward way to compute ratios in Excel.

Technique 3 – Custom Formatting

You can use the custom formatting option in Excel to simply divide the two numbers and display the result in the format of a fraction i.e. a/b. The limitation with this technique is that you can only display the result with the separator as a forward slash.

Follow these steps to compute ratios in Excel using custom formatting:

STEP 1: Divide the two numbers in cell D2.

STEP 2: Press Ctrl D to copy the formula down.

STEP 3: Select the range and press Ctrl 1 to open the Format Cells dialog.

STEP 4: Under the Number tab, choose the Custom option.

STEP 5: Enter the Ratio format (“#/#”). Then press OK.

The custom format i.e. a/b will be displayed in the range.

However, if you want to display the ratio in the format a:b, you should use the next technique.

Technique 4 – TEXT & SUBSTITUTE Functions

Another way to compute ratios in Excel is by combining TEXT and SUBSTITUTE functions. You first divide the two numbers and then use the TEXT function to display the decimal as a fraction. This fraction can then be changed from the a/b format to the a:b format using the SUBSTITUTE function.

The formula for this technique is:

=SUBSTITUTE(TEXT(Num1/Num2, “#/######”),”/”,”:”)

Follow these steps to achieve this result:

STEP 1: Enter the SUBSTITUTE function.

=SUBSTITUTE

STEP 2: Enter the TEXT function.

=SUBSTITUTE(TEXT

STEP 3: Enter the first argument of the TEXT function i.e. the division of the two numbers.

=SUBSTITUTE(TEXT(B2/C2

STEP 4: Enter the second argument of the TEXT function i.e. “#/###”. This is the format to display the result as a fraction.

=SUBSTITUTE(TEXT(B2/C2,”#/###”)

STEP 5: Enter the second argument of the SUBSTITUTE function i.e. the old text to be replaced. Here, it is a forward slash (/).

=SUBSTITUTE(TEXT(B2/C2,”#/###”),”/”

STEP 6: Enter the third argument of the SUBSTITUTE function i.e. the new text to replace the old text. Here, it is a colon (:).

=SUBSTITUTE(TEXT(A2/B2,”#/###”),”/”,”:”)

The ratio between goods produced and defective goods will be displayed in the format a:b.

Conclusion

In conclusion, this article discusses four techniques to compute ratios in Microsoft Excel, addressing different situations and preferences.

The Basic Division technique is straightforward but may not be suitable if the numbers do not divide evenly. The GCD Function technique utilizes Excel’s GCD function to ensure accurate ratios. The Custom Formatting technique is simple but limited to showing ratios with a slash (/). Finally, the TEXT & SUBSTITUTE Functions technique offers a flexible approach to compute and format ratios with a customizable separator, balancing precision and presentation.

Choose the technique that best suits your needs and the nature of your data.

Learn more about Formulas with our 101 Advanced Excel Formulas & Functions Examples.

The above is the detailed content of How to Calculate Ratio in Excel – 4 Useful Methods. For more information, please follow other related articles on the PHP Chinese website!