How do I display a ratio in Excel in the format A:B?

Question

I have an Excel spreadsheet of data like:

ColumnA ColumnB
33        11
25        5
6         4

What i would like to do is add a third column which shows the ratio of columnA to columnB in the format of A:B. For example:

ColumnA ColumnB   Ratio
33        11       3:1
25        5    5:1
6         4    3:2

Does anyone know of a way to achieve this?

Thanks in advance.

Answer 1

Try this formula:

=SUBSTITUTE(TEXT(A1/B1,"?/?"),"/",":")

Result:

A   B   C
33  11  3:1
25  5   5:1
6   4   3:2

Explanation:

• TEXT(A1/B1,"?/?") turns A/B into an improper fraction
• SUBSTITUTE(...) replaces the "/" in the fraction with a colon

This doesn't require any special toolkits or macros. The only downside might be that the result is considered text--not a number--so you can easily use it for further calculations.

---

Note: as @Robin Day suggested, increase the number of question marks (?) as desired to reduce rounding (thanks Robin!).