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:

The function TEXT(A1/B1,"?/?") converts A/B to an incorrect fraction.

SUBSTITUTE(...) substitutes a colon for the "/" in the fraction.

There are no special toolkits or macros required. The main drawback is that the result is text rather than a number, so you can easily utilise it in other calculations.