Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Removing decimals in column which has both numbers and text

Topic Labels: Formulas
Solved
Jump to Solution
1148 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!