Removing decimals in column which has both numbers and text

[lalosms]

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.

[kuovonne · Accepted solution]

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})
)

[lalosms]

Thank you! That has done it!