Skip to main content

Number formula not formatting

  • September 18, 2023
  • 1 reply
  • 22 views
R
Forum|alt.badge.img+10

I have 5 fields (WBS1, WBS2, WBS3, WBS4, WBS5) which are all different levels of WBS and I have a formula that combines it all into 1 number:

 
WBS1& IF(WBS2="","","."&WBS2 & IF(WBS3="","", "."&WBS3 &IF(WBS4="","","."&WBS4 &IF(WBS5="","","."&WBS5))))
 
The output of this formula is a number, like for example: 3.1.2 but it says the result type is not a number so I can't sort by it like I want to. I tried changing the "" to BLANK() like I saw on other posts but that isn't working. 

1 reply

A
Forum|alt.badge.img+17
  • Andy_Lin1
  • Inspiring
  • September 18, 2023

While the output of your formula is numbers and decimal points, it isn't understood by Airtable as a number because there are multiple decimal points so it can't be interpreted as either an integer or decimal (float) number. And I'm imagining that you'd want to sort by WBS1, then WBS2 .... So not a standard numerical sort either.

You have a couple of options:

  • use the sort feature and sort directly on your WBS fields
  • if WBS2 through WBS5 max out at 9 (or below), if you drop the decimal point before WBS3, WBS4, WBS5, Airtable can interpret the output as a number (note that Airtable's floating-point number handling is somewhat wonky)
  • pad your WBS numbers so that an "alphabetical" sort will work

The third option would look something like this:

RIGHT(REPT("0", 2) & WBS1, 2) &
IF(WBS2, "." & RIGHT(REPT("0", 2) & WBS2, 2) &
IF(WBS3, "." & RIGHT(REPT("0", 2) & WBS3, 2) &
IF(WBS4, "." & RIGHT(REPT("0", 2) & WBS4, 2) &
IF(WBS5, "." & RIGHT(REPT("0", 2) & WBS5, 2)
))))

The formula pads out each WBS with zeros so they're the same length; to adapt this to your base, change the number arguments of RIGHT() and REPT() to the length of the biggest WBS number (so if the biggest WBS is 100, that's three digits long, and instead of 2 in those functions, you'd use 3, like RIGHT(REPT("0", 3) & WBS5, 3).

Terms & ConditionsAccessibility statement