Help

Removing decimals in column which has both numbers and text

Topic Labels: Formulas
Solved
Jump to Solution
579 2
cancel
Showing results for 
Search instead for 
Did you mean: 
lalosms
4 - Data Explorer
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.

Screenshot 2022-11-25 at 14.40.11

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
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})
)

See Solution in Thread

2 Replies 2
kuovonne
18 - Pluto
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})
)

Thank you! That has done it!