**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(), aSUBSTITUTEandTEXTfunctions-based formula, and theROUND()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.

##### Table of contents

###### Key Takeaways

- The ratio in Excel formulahelps show the relationship between two numbers of the same units, which we can use to determine the bigger number out of the two.
- Users can use the ratio formula in Excel to check how many times a number contains a specific multiple. Also, the formula enables one to determine ratios such as the Individual and Group Compa-ratios.
- We can use the simple divide function,
**GCD()**,**SUBSTITUTE**and**TEXT**functions, and the**ROUND()**to build a ratio formula in Excel. - We can use the ratio formula to create more complex formulas such as a ratio and proportion formula and formula to determine the ratio of three numbers.

**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:

- Ensure the two values we must calculate the ratio for are in the same units and valid number format.
- Choose the target cell to display the required ratio value.
- 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. - 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:

- 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.
- Choose a target cell to calculate and display the required ratio.
- 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. - 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:

- First, ensure the two values we must calculate the ratio for have the same units and are correct and valid.
- Select a target cell to determine and display the required ratio.
- 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. - 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:

- First, ensure that the two values we must determine the ratio for have the same units and are valid and correct.
- Choose a cell to calculate and display the required ratio.
- 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. - 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)**

**1. How do I calculate Compa-ratio in Excel?**

We can calculate Compa-ratio in Excel using the following steps, explained with an example.

The following dataset lists employees, their designations, basic salaries, and the midpoint of their salary bands.

The requirement is to determine the Individual Compa-ratio of each employee and display the output in column E. Then, the steps are as follows:**Step 1: **Choose cell E2, enter the **GDC()**– based ratio formula, and press **Enter**.**=C2/GCD(C2,D2)&” : “&D2/GCD(C2,D2)****Step 2: **Using the fill handle, update the remaining target cells.

Thus, once we determine the Individual Compa-ratio of each employee, we can compare the employees’ salaries to the market standards.

If the ratio equals 1, the employee’s salary is similar to the market standards. On the other hand, for a ratio more or less than 1, the employee’s salary is greater or lower than the market standards.

**2. How to calculate ratio of three numbers in Excel?**

We can calculate ratio of three numbers in Excel using the following steps:

1. Ensure the three input values, for which we must determine the ratio, have the same units and are valid and correct.

2. Choose a target cell to determine and show the required ratio.

3. Enter one of the following formulas in the target cell:**=”1:”&value2/value1&” : “&value3/value1**

Or**=”1:”&TEXT(value2/value1,”0.00″)&” : “&TEXT(value3/value1,”0.00”)**

4. Press **Enter** to execute the formula and view the required ratio of the three input values in the target cell.

**3. How to calculate average ratio in Excel?**

We can calculate average ratio in Excel using the following steps:

1. Ensure the source dataset containing two sets of values is correct.

2. Choose a cell and enter the following formula to determine the average of the values in the first set.**=AVERAGE(value_set1_range)**

3. Next, select a cell and enter the following formula to determine the average of the values in the second set.**=AVERAGE(value_set2_range)**

4. Choose a cell and enter the following formula to determine the greatest common divisor of the two average values.**=GCD(AVERAGE(value_set1_range),AVERAGE(value_set2_range))**

5. Select a target cell and enter the following formula:**=AVERAGE(value_set1_range)/GCD(AVERAGE(value_set1_range),AVERAGE(value_set2_range))&” : “& AVERAGE(value_set2_range)/GCD(AVERAGE(value_set1_range),AVERAGE(value_set2_range))**

6. Press **Enter** to execute the formula and view the required average ratio in the target cell.

### 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 –

## Leave a Reply