Our formula columns will execute any valid code you send it, for better or worse. Streak will try to resolve some basic things automatically – adding "return" statements to simple math, etc – but sometimes you can get into a situation where a column returns both errors and proper calculations.
It's not always obvious where the problem originated either:
Hovering over the red triangle in the top-right corner of the cell will help troubleshoot
at a low-level.
Number errors
In the case above, the error message is Formula Returns an Unsupported Return Type: Cannot have a NaN or Infinite result. The formula itself looks like this:
= $'# of users' * $'Cost per user'
This error is because the third and fourth Cost per user rows are written as strings and not numbers (variables with only numbers) in the pipeline. There are two ways to approach a fix for this:
Change the original data. This is time-consuming for large data sets, and might involve training other users of the pipeline
Handle the error gracefully. This requires building some additional intelligence into your formula to make everything run smoothly.
Declare variables
Before proceeding, it's worth giving your variables easier to write (and use) names. This will make your formula easier to understand in the short-term and more efficient in the long-term.
For the formula above, we can make it a bit easier to work with by declaring variables at the top, and returning the result of multiplying them:
= var num = $'# of users'
var cost = $'Cost per user'
return num * cost
Remove error results
That formula can return inaccurate results in the above case when you present it with incomplete information (such as $0.00 for every row with incomplete information).
You can remove those by forcing it to return either a valid result, or (the || characters below) nothing:
return (num * cost)||null
This removes bad results, but it doesn't fix the core issue: the bad data leading to the wrong calculations.
Convert strings to numbers
Because the type isn't predictable, you have to build a bit of logic into the formula. You can check type using an if statement in tandem with typeof :
if(typeof cost === 'string') { }
Within that if statement you can redefine cost and mutate it to remove any non-number (and non-decimal) characters:
if(typeof cost === 'string') {
var cost = parseFloat(
cost.replace(/[^[0-9.]]*/g, "")
)
}
If you place that block of code between the declaration of variables and your return statement, you should have a formula block that looks like this:
= var num = $'# of users'
var cost = $'Cost per user'
if(typeof cost === 'string') {
var cost = parseFloat(
cost.replace(/[^[0-9.]]*/g, "")
)
}
return (num * cost)||null
This will result in a pipeline that looks like this:
