Ratio In Excel Formula

What Is Ratio In Excel Formula?

The Ratio in Excel formula enables one to determine the relation between two values of the same units and compare them to assess how big one value is than the other. We can use the simple divide function, GDC(), a SUBSTITUTE and TEXT functions-based formula, and the ROUND() to calculate a ratio in a worksheet.

Users can use the ratio in Excel formula to calculate ratios such as average ratio and Individual Compa-ratio.

For example, the following dataset lists two sets of values.

The requirement is to determine the ratio of the first and second values in each row and display the ratios in the corresponding cells in column C.

Then, we can determine the required value in each target cell using ratio in Excel formula based on the simple divide function.

In the above find ratio in Excel formula example, the logic of each target cell formula is that the corresponding column A value is divided by the column B value. Next, the division output gets concatenated with “ : ” and 1 to get the required ratio in Excel.

For instance, we shall check the cell C2 formula to understand how the simple divide function-based formula works.

First, the function divides the value of 10 by 5, which results in the value of 2. Next, the formula concatenates the value of 2 with a Colon and the value 1. Thus, we obtain the required ratio of the values 10 and 5, 2 : 1, in cell C2.

How To Calculate Ratio In Excel?

We shall see how to calculate ratios using ratio in Excel formula with practical examples.

#1 – Simple Divide Function

The simple divide function helps create the most straightforward determine ratio in Excel formula. The formula involves the division symbol, “/”, to divide two values and the “&” to concatenate the division output with “:” and the value of 1.

The steps to use the simple divide function-based find ratio in Excel formula are as follows:

1. Ensure the two values we must calculate the ratio for are in the same units and valid number format.
2. Choose the target cell to display the required ratio value.
3. Enter the below formula in the target cell.
   =number1/number2&” : “&”1”
   Where number1 and number2 can be values, Excel cell references to the values, functions or formulas returning the required values.
4. Press Enter to execute the formula and view the required ratio in the target cell.

Example Of Simple Divide Function

The following dataset lists numbers and their multiples.

The requirement is to calculate the ratio between the number and its multiple in each row to determine how many times the number contains the multiple. Assume the target cells are cells C2:C6.

Then, we can use a simple divide function-based determine ratio in Excel formula in the target cells to achieve the required outcome.

Step 1: Choose cell C2 and enter the simple divide function-based formula using the cell references to the corresponding number and multiple values in row 2.

Step 2: Press Enter to execute the simple divide function-based formula and view the required ratio in cell C2, 20 : 1.

Step 3: Using the Excel fill handle, update the formula in the remaining target cells C3:C6.

Let us check the cell C6 formula to understand how the expression works.

First, the formula divides 558 by 93, resulting in 6. Next, it concatenates the value of 6 with “ : ” and 1 to return 6 : 1 as the required ratio.

Thus, the output shows that the number of times 558 contains the multiple 93 is 6.

#2 – GCD Function

We can create the ratio in Excel formula using the GCD(), which returns the specified integers’ greatest common divisor (GCD).

The GCD() syntax is as follows:

The function takes one mandatory argument, number1, and up to 254 optional arguments, number2, number3 and so on. It finds the largest integer, dividing the specified numbers without a remainder.

So, the steps to calculate a ratio using the GCD()- based ratio formula in Excel are as follows:

1. First, ensure the two values we must determine the ratio for have the same units and they are not non-numeric or negative to avoid potential errors.
2. Choose a target cell to calculate and display the required ratio.
3. Enter the below formula in the target cell:
   =value1/GCD(value1, value2)&” : “&value2/GCD(value1, value2)
   Where value1 and value2 can be values, cell references to the values, functions or formulas returning the required values.
4. Press Enter to execute the formula and achieve the required ratio in the chosen cell.

Example Of GCD Function

The following dataset lists different counts of two types of flowers, Roses and Peonies.

The aim is to determine the count of bouquets in which we can have both types of flowers (Roses and Peonies), with the total number of flowers being the same. Also, we must calculate the ratio of the two flower types in each bouquet.

Then, the steps are as follows:

Step 1: Choose cell C2, enter the GCD(), cited in the Formula Bar, and press Enter.

Next, update the function in the remaining cells C3:C7 using the fill handle.

Step 2: Select cell D2, enter the following formula, and press Enter.

=A2/C2&” : “&B2/C2

Next, use the fill handle to update the formula in the remaining cells D3:D7.

We shall check the row 7 formulas in cells C7 and D7 to understand how they work.

The GCD() accepts the Roses and Peonies counts, 158 and 150, and returns the largest integer dividing the specified values without a remainder (i.e., GCD), 2. Thus, we can have two equally-sized bouquets containing both types of flowers.

Next, the cell D7 formula divides the Roses and Peonies counts by the determined GCD, with the results of the divisions being 79 and 75. Finally, the formula concatenates 79, “ : ”, and 75 to return the required ratio.

Thus, the cell D7 formula indicates that each of the two bouquets can have 79 Roses and 75 Peonies to ensure that each bouquet has the same size and contains both flower types.

#3 – SUBSTITUTE And TEXT Function

Sometimes, we may be required to show the ratio of the given values as a text. In such cases, we can use the Excel TEXT function to get the output of dividing the two given values as a text value containing “/“. After that, we can use the Excel SUBSTITUTE function to replace “/”with “ : ” to view the resulting text value in the ratio format.

So, here is how to calculate a ratio using the SUBSTITUTE and TEXT functions-based ratio in Excel formula:

1. First, ensure the two values we must calculate the ratio for have the same units and are correct and valid.
2. Select a target cell to determine and display the required ratio.
3. Enter the below formula in the target cell:
   =SUBSTITUTE(TEXT(number1/number2,”#####/#####”),”/”,” : “)
   The number1 and number2 arguments can be values, cell references to the values, functions or formulas returning the required values.
4. Press Enter to execute the formula and achieve the required ratio in the chosen cell.

First, the formula divides the first number by the second number. Next, the TEXT() accepts the division output as the first argument, value, and the required format to display the division output as the second argument, format_text.

After that, the SUBSTITUTE() accepts the TEXT() output, which is a text value containing a numerator, “/”, and denominator, as the first argument text. Next, the function accepts “/”and “ : ” as the second and third arguments, old_text and new_text. Thus, the SUBSTITUTE() replaces “/”with “ : ” in the TEXT() output to display the text value in the ratio format.

Example Of SUBSTITUTE And TEXT Function

We shall see an example of a ratio and proportion formula in Excel using the SUBSTITUTE and TEXT functions.

The following dataset shows two value sets containing the number of books read in the cited hours.

The requirement is to determine the ratio and proportion for the two sets of values and display the output in column E cells.

Then, here is how to use a SUBSTITUTE and TEXT functions-based ratio and proportion formula in Excel.

Step 1: Choose cell E2 and enter the following formula.

=SUBSTITUTE(TEXT(A2/B2,”#####/#####”),”/”,” : “)&” :: “&SUBSTITUTE(TEXT(C2/D2,”#####/#####”),”/”,” : “)

Step 2: Press Enter to implement the formula in the target cell.

Step 3: Using the fill handle, update the formula in the remaining target cells.

Let us check the cell E4 formula to understand its logic.

First, the formula divides the cells A4 and B4 values, which results in the value of 5.3333333. Next, the TEXT() shows the value 5.3333333 in a format containing “/”, 16/3. After that, the SUBSTITUTE() replaces “/” in the text value 16/3 with “ : ”, resulting in the text value appearing as a ratio of 16 : 3.

Next, the value 16 : 3 gets concatenated with “ :: ” to result in the term “16 : 3 :: ”.

Likewise, the second SUBSTITUTE(), containing the TEXT(), which works in the same way as explained previously, displays the text 16/3 as a ratio of 16 : 3.

Finally, the formula concatenates the second SUBSTITUTE() output with “16 : 3 :: ” to return the required ratio and proportion for the given sets of values, 16 : 3 :: 16 : 3.

So, the output shows that the two sets of data are proportional.

#4 – ROUND Function

The Excel ROUND function rounds off the given number to the specified number of digits. We can use it to round off the value obtained by dividing two given numbers.

After that, it becomes quite straightforward to concatenate the output with a Colon and the value of 1 to obtain the required ratio.

The steps to determine a ratio using the ROUND()– based ratio in Excel formula are as follows:

1. First, ensure that the two values we must determine the ratio for have the same units and are valid and correct.
2. Choose a cell to calculate and display the required ratio.
3. Type the following formula in the target cell:
   = ROUND(number1/number2,1)&” : “&1
   Where number1 and number2 can be values, cell references to the values, functions or formulas returning the required values.
4. Press Enter to execute the formula and achieve the required ratio in the chosen cell.

Example Of ROUND Function

The following dataset lists five fruit bowls containing apples and oranges.

The requirement is to determine the ratio of apples and oranges in each fruit bowl and display the output in column E.

Then, the steps are as follows:

Step 1: Choose cell E2 and enter the following formula:

=ROUND(C2/D2,1)&” : “&1

Step 2: Press Enter to view the formula output in the target cell.

Step 3: Using the fill handle, update the formula in the remaining target cells.

Let us check the cell E6 formula to understand the logic.

First, the formula divides the values in cells C6 and D6, 78 and 13, which results in the value 6. Next, the ROUND() rounds off the value 6 to one digit, 6.

Next, the formula concatenates the ROUND() output 6 with “ : ” and 1 to return the required ratio of the two fruits in the fifth fruit bowl as 6 : 1.

Important Things To Note

• Ensure the values supplied as inputs in the ratio in Excel formula are valid numbers and have the same units.
• The two values for which we must determine the ratio must be positive. Also, the second value should not be zero.
• The ratio formula in Excel only displays the output in the ratio format. Using the value for further mathematical calculations may not give the correct results.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the Ratio In Excel Formula, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Ratio In Excel Formula. We explain how to find ratios using simple divide, GCD, SUBSTITUTE & TEXT, & ROUND functions, with examples. You may learn more from the following articles –

• N Excel
• Divide In Excel
• Excel As Calculator