Use Formulas

How to empower your sheets with 100+ BI-ready custom formulas

Written By Vins

Last updated About 14 hours ago

Overview

Formulas in DataStripes allow you to perform calculations, analyze data, and automate workflows using a rich set of Excel-compatible and custom functions. All formulas are evaluated in real time and support references across sheets.

Entering Formulas

  1. Select a cell and type = to begin a formula.

  2. Use standard Excel functions (e.g., =SUM(A1:A10), =AVERAGE(B2:B20)).

  3. Use custom DataStripes functions for advanced analysis (e.g., =NPV(0.08, C2:C6), =FILTER(A1:C10, "B>0")).

  4. Reference other sheets using SheetName!A1 syntax.

  5. Press Enter to calculate and display the result.

Advanced Formula Features

  • Auto-complete: Start typing a function name to see suggestions and argument hints.

  • Formula Bar: Use the formula bar for editing long or complex formulas.

  • Array Formulas: Many functions return arrays that spill over multiple cells (e.g., =FILTER(...)).

  • Error Handling: Errors are shown directly in the cell with helpful messages.

  • Nested Functions: Combine multiple functions for powerful calculations (e.g., =SUM(FILTER(A1:A100, "A>0"))).

Supported Function Categories

  • Financial (NPV, IRR, ROI, etc.)

  • Array & Transformation (FILTER, SORT, UNIQUE, etc.)

  • Text (REGEX_EXTRACT, SLUG, etc.)

  • Date (DAYS_FROM_TODAY, AGE, etc.)

  • Statistics & Prediction (FORECAST, DESCRIBE, etc.)

  • Validation (IS_EMAIL, IS_URL, etc.)

  • Data Quality (ANONYMIZE, FUZZY_DEDUPE, etc.)

  • Advanced Analytics (GROUP_BY, PIVOT, etc.)

  • Data Connectors (HTTP_GET, STOCK, FX_RATE, etc.)

Tips & Best Practices

  • Use parentheses to control calculation order.

  • Combine custom and standard functions for advanced logic.

  • Use the fill handle to copy formulas across ranges.

  • Document complex formulas with cell comments.

Troubleshooting

  • If a formula returns an error, check for typos, missing arguments, or invalid references.

  • Circular references are not supported and will show an error.

  • For performance, avoid volatile functions in very large sheets.

Formulas are the heart of DataStripes—explore the full function list in the documentation and experiment with combining them for powerful analytics.