Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

# Removing decimals in column which has both numbers and text

Topic Labels: Formulas
Solved
234 2
cancel
Showing results for
Did you mean:
4 - Data Explorer

Hi all,

New to formulas in Airtable and need a bit of help. My Formula is designed to complete an equation from two different columns, and if the result returns as an error in a cell, then to replace it with a message. The formula can be seen here.

IF(
ISERROR({TSINGLES} / ({LM}-{TSINGLES})),“No Sales”,{TSINGLES} / ({LM}-{TSINGLES}))

Ultimately, as the column now contains numbers and also text, I can’t implement formatting in the dropdown at the top of the column.

Could I add something to my formula to ensure that the result from the calculation are returned without any decimals? Here is a screenshot of what we’re working with - not brilliant.

1 Solution

Accepted Solutions
18 - Pluto

Welcome to the Airtable community!

You can get rid of the decimal by rounding the answer.

``````IF(
ISERROR({TSINGLES} / ({LM}-{TSINGLES})),
"No Sales",
ROUND({TSINGLES} / ({LM}-{TSINGLES}))
)
``````

The error comes from trying to divide by zero, so you could change the formula like this …

``````IF(
{LM} = {TSINGLES},
"No Sales",
ROUND({TSINGLES} / ({LM}-{TSINGLES}))
)
``````

Another option is to leave the cell blank when there are no sales and use the cell formatting to take care of decimals.

``````IF(
{LM} != {TSINGLES},
{TSINGLES} / ({LM}-{TSINGLES})
)
``````
2 Replies 2
18 - Pluto

Welcome to the Airtable community!

You can get rid of the decimal by rounding the answer.

``````IF(
ISERROR({TSINGLES} / ({LM}-{TSINGLES})),
"No Sales",
ROUND({TSINGLES} / ({LM}-{TSINGLES}))
)
``````

The error comes from trying to divide by zero, so you could change the formula like this …

``````IF(
{LM} = {TSINGLES},
"No Sales",
ROUND({TSINGLES} / ({LM}-{TSINGLES}))
)
``````

Another option is to leave the cell blank when there are no sales and use the cell formatting to take care of decimals.

``````IF(
{LM} != {TSINGLES},
{TSINGLES} / ({LM}-{TSINGLES})
)
``````
4 - Data Explorer

Thank you! That has done it!