Stackby Formulas - The Complete Guide & Common Errors
From analyzing data on paper to doing it on a spreadsheet, we’ve come along a long way.
Even though Stackby was built to create complex business workflows easily, you can also use it to create complex reports quickly using inbuilt formulas and functions.
I know a lot of you must be familiar with how to use formulas on Google Sheets or Excel. But Stackby formulas are different than that of spreadsheets, it’s not cell-based like A1, B2, C3; it’s column based and takes the schema of {Column Name 1}, {Column Name 2} in curly brackets.
It supports over 100+ formulas from everything to adding to numbers to using IF, ELSE conditions. Today, we’ll look at the top 20 formulas you can use on Stackby right away -
1. Basic Numeric Formula
You can easily add, subtract, multiply and divide numbers by just adding a formula to a column. Just ensure the column type you’re multiplying are integers.
For example, if you need to multiply two numbers then you can select a new column, choose its type as Formula and enter formula as -
{column 1*column 2}
Just remember to use the exact column name you want to multiply.
2. Numerical Functions
Stackby supports all popular numerical functions like Average, Sum, Minimum, Maximum, Absolute value and more.
You just need to enter the column names inside the function to get the relevant answer. For example if I need to add three numbers in different columns, I’ll enter the function as -
SUM({column1},{column2},{column3})
Similarly for calculating the average of three numbers you need to enter -
AVG({column1},{column2},{column3})
3. Logical Operators
You can use logical operators like greater than, equal to and more in your formulas. The main usage is inside formulas. For example -
IF(({column1}>{column2}),1,0)
This function will give 1 as the output when the first column is greater than the second column and 0 when it's not. Similarly you can use other logical operators.
The IF formula is denoted by IF(condition,true_value,false_value). The function allows you to fill the sheet with value (integer or text) whether the condition is true or false.
Let me explain this to you by an example of a school performance report. Let’s assume a school wants to understand which students have passed or failed depending on the total scores. They can easily do this by entering a IF function that is
IF({Total marks out of 100}<35,"fail",IF({Total marks out of 100}>35,"pass"))
Below is a sample output -
You can also add more grades by adding more IF conditions. It’s a classic example of nested if.
OR Function - This function tests the conditions and returns TRUE or FALSE when either of the condition is true.
OR(({Marks}=35),({Marks}>35))
AND Function - This function tests the all the conditions and returns TRUE only when all the conditions are satisified or the output is FALSE.
AND(({Marks 1}>35),({Marks 2}>35))
4. True or False
Want to check whether you’ve achieved your monthly sales target or whether the website traffic has exceeded what you had aimed for? Use the TRUE or FALSE formulae to exactly do that. The function returns the output as 0 and 1 depending upon the condition.
For example you’ve set a traffic goal to be 5k visitors. To check this you can enter the formulae as -
TRUE({Traffic}>5000)
5. Date Functions
The Date function allows you to calculate the time frame between two dates or relative time to now or from now. Below are few examples of date functions -
DATE_DIFF() - Calculates the difference between two dates. Example - get marketing data for a specific period.
DATE_DIFF({Date},{Date 2})
TO_DATE() - Calculates the relative time to date. Example - calculating the age of a person.
TO_DATE({Date})
FROM_DATE() - Calculates the relative from date. Example - time left before a project deadline.
FROM_DATE({Date})
WEEKDAY() - Returns the day of the week as an integer where Sunday is denoted as zero and Saturday is denoted as six.
WEEKDAY({Date})
DATE_ADD() - This function allows you to calculate a date upon the addition of a set number of days.
DATE_ADD({Date},{Days after},"days")
6. Additional Functions
In addition to the common functions you can also use other functions. Here are few and what you can use them for -
HYP(“URL”,”text”) - Converts a text into a hyperlink.
HYP("URL",{Text})
SET_TIMEZONE(datetime, timezone) - Sets a specific timezone for a datetime.
SET_TIMEZONE(NOW(),"Australia/Syndey")
Pro Tip - To bring your reports to life you can also add emojis to your reports. Taking the same example of the school assigning grades to students, you can add emojis such as a tick for pass and cross for fail. You can do this by using the IF function.
7. Text Functions
Text formulas lets you apply formulas on text strings. You can use it to concatenate two strings, trim it by characters and more. Here are few formulas you can try -
CONCAT("string1","string2")
The CONCAT function allows you to append two or more columns together. You can also add character and spaces in between.
For example if I want to append the first name and the second name. I would enter the formula as -
CONCAT({first_name},” “,{second_name})
Remember never to forget entering column names under curly brackets and text in double apostrophe.
Here's the output -
LEFT(“string”,how many) - You can use this function to extract certain characters from a string.
LEFT(“sample text”, 5)
MID(“string”,where to start, how many) - This formula extracts a substring from “where to start”.
MID({String},5,3)
LEN() - This function allows you to calculate the number of characters in a string (text).
LEN({String})
Debugging the common error types
When using formulas on Stackby you’re ought to find errors. Below we’ve listed common errors and how you can solve them.
#ERROR- This error occurs when the names of the columns or any other details aren’t entered correctly or are missing.
Invalid Formula- If a formula is incomplete, wrongly typed or does not contain all the objects that it needs, you will get the error as invalid formula.
NaN- You get this error when the formatting of the referenced cell is not a numerical value. Remember to change the formatting depending on the value you’re entering.
Circular reference- This error occurs when you have unknowingly referenced the formula’s result into the formula. Remember to keep all the objects unique.
Infinity- If a formula gives out a value that is infinity you can get this error. To solve this you may want to check the formulae of the inputs.
Over to you
Hope you found the above formulas interesting. They can be really helpful in creating automated reports and to analyze data better. See the power and usability yourself by trying the formulas on Stackby today.
