Use Custom Formulas

Overview

Custom formulas in DataStripes extend standard spreadsheet capabilities with powerful functions for finance, arrays, text, dates, statistics, validation, data quality, analytics, and more.

Using Custom Formulas

1. In any cell, type = and use custom functions such as:

   • Financial: =NPV(rate, values...), =IRR(range, [guess]), =ROI(gain, cost)

   • Array & Transformation: =FILTER(range, cond...), =SORT(range, col, asc), =UNIQUE(range)

   • Text: =REGEX_EXTRACT(text, pattern), =SLUG(text), =CLEAN_TEXT(text)

   • Date: =DAYS_FROM_TODAY(date), =AGE(birthdate), =QUARTER(date)

   • Statistics & Prediction: =FORECAST(x, known_y, known_x), =DESCRIBE(range), =ZSCORE(range)

   • Validation: =IS_EMAIL(text), =IS_URL(text), =IS_PHONE(text)

   • Data Quality: =ANONYMIZE(range), =FUZZY_DEDUPE(range)

   • Advanced Analytics: =GROUP_BY(range, group_cols, agg_cols, agg_fns), =PIVOT(range, key_col, val_col, [agg])

   • Data Connectors: =HTTP_GET(url, [path], [key]), =STOCK(ticker, [field]), =FX_RATE(from, to)

   • User & RLS: =USER(), =USER("email"), =USER("tier")

2. Press Enter to calculate and see the result.

Advanced Usage

• Combine custom and standard functions for complex logic (e.g., =IF(IS_EMAIL(A2), "Valid", "Invalid")).

• Use array formulas to process ranges and return multi-cell results.

• Reference other sheets or dynamic ranges in your formulas.

Tips & Best Practices

• See the full list of custom formulas in the documentation or formula helper.

• Use comments to document the purpose of complex formulas.

• Test formulas on sample data before applying to large datasets.

Troubleshooting

• If a custom formula returns an error, check argument types and required parameters.

• Some functions require numeric or date inputs—verify your data types.

• For connector formulas, ensure you have an active internet connection.

Custom formulas unlock advanced analytics in DataStripes—experiment and combine them for maximum insight.