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?

Mar 30, 2022 in Database 550 views

## 1 answer to this question.

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.

• 23,260 points

## How do I split a cell in Excel into two or more where they are divided horizontally?

Table cells should be combined. To make a ...READ MORE

## How do I list all the columns in a table?

For the various popular database systems, how ...READ MORE

## How to display the queries executed by a particular user in MySQL?

From the version 5.1.7 onward, MySQL allows ...READ MORE

## How do I reset a sequence in Oracle?

If you want to set it to ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

Forget the PsychoPy complications for the time ...READ MORE

## In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

## Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE