# Calculate Ratios in Excel

This post will demonstrate how to calculate and display ratios in Excel.

Suppose you want to show the ratio of Managers to Team Members or the ratio of Managers to all employees.

We also need to make sure that the ratio is reduced to the simplest value.

Let’s check it out.

# What are Ratios and How Do We Write Them?

For our starting example, we have defined a list of 3 Managers and 7 Team Members.

If we were to write the ratio of Managers to Team Members as expressed verbally, we would write something like “**3 to 7**”.

This means for every **3 Managers** we have **7 Team Members**.

A more common way to express this ratio is to use a **colon** ( **:** ) in place of the word “**to**”.

We don’t want to write this statically; we want the ratio to change based on what we place in cells **B3** and **B4**.

This would appear at face value to be nothing more than a simple concatenation of cell contents.

We could write a formula that says, “take the contents of cell **B3** and concatenate (*i.e., put together with*) it with a colon then concatenate to that the contents of cell **B4**.”

=B3 & “:” & B4

This is easy, but what about a ratio like the following?

The answer is technically correct, but most people prefer the result to be reduced to its simplest form. In other words, instead of writing **10:50**, we would rather show **1:5**.

# Reducing Ratios to Their Simplest Form

We need to discover the **Greatest Common Factor** and divide each value by that factor.

In our above example using **10:50**, the **Greatest Common Factor** between **10** and **50** is **10**.

If we divide each value by **10**, the result would be **1:5**.

To discover this divisor, we will use the Excel function **GCD**.

The **GCD** (*or Greatest Common Divisor*) function returns the greatest common divisor of two or more integers. The greatest common divisor is the largest integer that divides both number1 and number2 without a remainder.

The function syntax is as follows:

**GCD( number1, [number2], … )**

*only number1 is required*

You can list up to 255 values, and any non-integer values will be truncated.

In our example, we could write the formulas as follows.

=GCD(B3, B4) or =GCD(B3:B4)

As our numbers are directly next to one another, either of these formulas will work.

To reduce the ratios by this **GCD** result, we divide each side of the ratio using the above GCD formula.

=B3 / GCD(B3, B4) & “:” & B4 / GCD(B3, B4)

# Dynamically Determining the Ratio Values

It’s one thing to have the ratio values hard-coded into cells, but suppose you must calculate the number of managers and Team Members from a list of employees?

Our list of employees is a proper Excel table named “**Employees**”.

We can determine the number of **Managers** and **Team Members** in the **Employees** table using the **COUNTIFS** function.

=COUNTIFS(Employees[Role], A3)

=COUNTIFS(Employees[Role], A3)

Because we are using a proper Excel Table, adding or removing rows from the Employees table will automatically be seen by the **COUNTIFS** functions to determine the new ratios.

# BONUS TIP – Using the LET Function to Enhance Calculations

We could combine all the intermediate calculations into the ratio calculation to perform this in a single step…

=COUNTIFS(Employees[Role], A3) / GCD(COUNTIFS(Employees[Role], A3), COUNTIFS(Employees[Role], A4)) & “:” & COUNTIFS(Employees[Role], A4)/ GCD(COUNTIFS(Employees[Role], A3), COUNTIFS(Employees[Role], A4))

…but as you can see, it can get a bit confusing.

This is the kind of formula Excel Geeks show off to their friends at “the meetings” but also the kind of formula that normal users run from screaming in a state of panic.

From a performance point of view, we are performing the same calculations multiple times; each COUNTIFS for determining the number of **Managers** is performed 3 times and the same for the COUNTIFS for determining **Team Members**.

This is where the **LET** function can bring a sense of elegance as well as speed to your calculations.

If you are unfamiliar with the new Excel **LET** function, check out this post and video demonstrating this amazing new function.

### Excel’s New LET Function (Streamlining Formulas)

To use the **LET** function to create this ratio, we begin by initiating a **LET** function.

=LET(

Next, we create a name for the formula, function, or reference we want to reuse.

Because we are calculating the left and right sides of a ratio, we will call out formulas that determine those values “Left_Side” and “Right_Side”.

To define the “Left_Side” we declare the name of the reference (“**Left_Side**”) and the formula to execute when called (“**COUNTIFS(Employees[Role], A3)**”)

=LET(Left_Side, COUNTIFS(Employees[Role], A3)

Now we do the same thing for establishing the name and formula for the “**Right_Side**” of the ratio.

=LET(Left_Side, COUNTIFS(Employees[Role], A3), Right_Side, COUNTIFS(Employees[Role], A4)

Finally, we write the formula that will utilize these named formulas to perform the ratio calculation. This formula will use the results of “Left_Side” and “Right_Side” along with the **GCD** formulas from earlier.

=LET(Left_Side, COUNTIFS(Employees[Role], A3), Right_Side, COUNTIFS(Employees[Role], A4), Left_Side / GCD(Left_Side, Right_Side) & “:” & Right_Side / GCD(Left_Side, Right_Side) )

If it helps, here is how the formula appears in Excel’s **Formula Bar** (*note: I am using Alt-Enter keystrokes to embed in-cell carriage returns to make the formula easier to read.*)

This may seem like more work than before (*it’s only just slightly more work*), but the pay-off is that each **COUNTIFS** formula only needs to be calculated a single time. The results are then reused throughout the formula whenever called upon.

## “Warp Speed, Mr. Scott!!!”

If you are one of those types of users that wants to squeeze every drop of performance out of your formulas (*and your long game is job security*), you can take the **LET** function a step further and create a named reference for the **GCD** portion of the formula.

=LET(Left_Side, COUNTIFS(Employees[Role], A3), Right_Side, COUNTIFS(Employees[Role], A4), My_Ratio, GCD(Left_Side, Right_Side), Left_Side / My_Ratio & “:” & Right_Side / My_Ratio)

## Practice Workbook

Feel free to Download the Workbook HERE.