Number formula not formatting

Topic Labels: Formulas
102 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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 1

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