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)
Featured Course
Excel Essentials for the Real World
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.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
