Working with Formulas¶

CalcBridge supports over 50 Excel-compatible functions with a high-performance formula engine. This guide covers formula syntax, supported functions, cell references, and validation.

Formula Syntax¶

CalcBridge formulas follow standard Excel syntax:

=FUNCTION(argument1, argument2, ...)

Basic Rules¶

Rule	Example
Formulas start with `=`	`=SUM(A1:A10)`
Functions are case-insensitive	`=sum(A1:A10)` works
Arguments separated by commas	`=IF(A1>0, "Yes", "No")`
Text enclosed in double quotes	`="Hello World"`
Ranges use colon notation	`=A1:A10`
Multiple conditions use nested functions	`=IF(AND(A1>0, B1<100), ...)`

Supported Operators¶

Operator	Description	Example
`+`	Addition	`=A1+B1`
`-`	Subtraction	`=A1-B1`
`*`	Multiplication	`=A1*B1`
`/`	Division	`=A1/B1`
`^`	Exponentiation	`=A1^2`
`&`	Concatenation	`=A1&" "&B1`
`=`	Equal to	`=IF(A1=B1, ...)`
`<>`	Not equal to	`=IF(A1<>B1, ...)`
`>`	Greater than	`=IF(A1>100, ...)`
`<`	Less than	`=IF(A1<100, ...)`
`>=`	Greater than or equal	`=IF(A1>=100, ...)`
`<=`	Less than or equal	`=IF(A1<=100, ...)`

Supported Functions Overview¶

CalcBridge supports 50+ Excel functions organized by category. For the complete reference with syntax and examples, see Excel Functions Reference.

Logical Functions¶

Function	Description
`IF`	Conditional evaluation
`IFS`	Multiple conditions
`IFERROR`	Error handling
`AND`	Logical AND
`OR`	Logical OR
`NOT`	Logical NOT
`SWITCH`	Match value against list
`CHOOSE`	Select from list by index

Example:

=IF(AND(A1>0, A1<100), "In Range", "Out of Range")

Lookup Functions¶

Function	Description
`VLOOKUP`	Vertical lookup
`HLOOKUP`	Horizontal lookup
`XLOOKUP`	Modern lookup
`INDEX`	Return value by position
`MATCH`	Find position of value
`XMATCH`	Modern match

Example:

=XLOOKUP(A1, $B$1:$B$100, $C$1:$C$100, "Not Found")

Math Functions¶

Function	Description
`SUM`	Sum of values
`SUMIF`	Conditional sum
`SUMIFS`	Multiple condition sum
`SUMPRODUCT`	Sum of products
`ABS`	Absolute value
`ROUND`	Round to decimals
`ROUNDUP`	Round up
`ROUNDDOWN`	Round down
`MOD`	Modulo
`POWER`	Exponentiation

Example:

=SUMIFS(D:D, A:A, ">=2025-01-01", B:B, "Active")

Statistical Functions¶

Function	Description
`COUNT`	Count numeric cells
`COUNTA`	Count non-empty cells
`COUNTIF`	Conditional count
`COUNTIFS`	Multiple condition count
`AVERAGE`	Average of values
`AVERAGEIF`	Conditional average
`MIN`	Minimum value
`MAX`	Maximum value
`MEDIAN`	Median value

Example:

=COUNTIFS(Rating:Rating, "CCC", Status:Status, "Active")

Text Functions¶

Function	Description
`CONCATENATE`	Join text
`CONCAT`	Modern join
`TEXTJOIN`	Join with delimiter
`LEFT`	Left characters
`RIGHT`	Right characters
`MID`	Middle characters
`LEN`	Text length
`TRIM`	Remove extra spaces
`UPPER`	Uppercase
`LOWER`	Lowercase

Example:

=TEXTJOIN(", ", TRUE, A1:A10)

Date Functions¶

Function	Description
`DATE`	Create date
`TODAY`	Current date
`NOW`	Current date/time
`YEAR`	Extract year
`MONTH`	Extract month
`DAY`	Extract day
`DATEDIF`	Date difference
`EDATE`	Add months
`EOMONTH`	End of month

Example:

=DATEDIF(A1, TODAY(), "M")

Cell References¶

Reference Types¶

Type	Syntax	Behavior
Relative	`A1`	Changes when formula is copied
Absolute	`$A$1`	Fixed when formula is copied
Mixed	`$A1` or `A$1`	Column or row fixed

Reference Examples¶

=A1         # Relative reference
=$A$1       # Absolute reference
=$A1        # Absolute column, relative row
=A$1        # Relative column, absolute row

Range References¶

=A1:B10     # Range from A1 to B10
=A:A        # Entire column A
=1:1        # Entire row 1
=A1:A       # From A1 to end of column A

Cross-Sheet References¶

CalcBridge supports references to other sheets within the same workbook.

Syntax¶

='Sheet Name'!CellReference

Examples¶

# Reference to cell A1 on Sheet2
=Sheet2!A1

# Sheet name with spaces
='Portfolio Data'!B5

# Range on another sheet
=SUM('Holdings'!A1:A100)

# Lookup across sheets
=VLOOKUP(A1, 'Reference Data'!A:C, 3, FALSE)

Cross-Sheet Best Practices¶

Use absolute references when referring to lookup tables
Name the target sheet clearly to improve readability
Avoid circular cross-sheet references
Test references after renaming sheets

Sheet Name Changes

If you rename a sheet, cross-sheet references are automatically updated. However, deleted sheets will cause #REF! errors.

Formula Validation¶

CalcBridge validates all formulas to ensure they are correct and safe to execute.

Validation Checks¶

Check	Description
Syntax	Formula follows Excel syntax rules
Functions	All functions are supported
References	Cell references are valid
Dependencies	No circular references
Security	No unsafe operations

Validation via UI¶

When you enter a formula:

CalcBridge parses and validates in real-time
Syntax errors display immediately below the cell
Valid formulas show a green checkmark
Invalid formulas show a red warning

Validation via API¶

curl -X POST https://api.calcbridge.io/api/v1/calculations/validate \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"formula": "=SUM(A1:A10)"}'

Response (valid):

{
  "valid": true,
  "formula": "=SUM(A1:A10)",
  "functions_used": ["SUM"],
  "cell_references": ["A1:A10"]
}

Response (invalid):

{
  "valid": false,
  "formula": "=UNKNOWNFUNC(A1)",
  "error": "Unknown function: UNKNOWNFUNC",
  "position": 1
}

Common Validation Errors¶

Error	Cause	Solution
`Unknown function`	Function not supported	Check supported functions
`Invalid syntax`	Missing parenthesis, etc.	Check formula structure
`Invalid reference`	Reference to non-existent cell	Verify cell reference
`Circular reference`	Formula references itself	Break the dependency cycle
`Type mismatch`	Wrong argument type	Check function parameters

Formula Best Practices¶

Readability¶

Use named ranges for frequently referenced ranges
Break complex formulas into multiple cells
Add comments (via cell notes) to explain complex logic
Use consistent formatting for similar formulas

Performance¶

Avoid entire column references (A:A) when possible
Use SUMIFS instead of SUMPRODUCT for better performance
Minimize cross-sheet references in frequently calculated cells
Use INDEX/MATCH instead of VLOOKUP for large datasets

Error Handling¶

Wrap lookups in IFERROR to handle missing values
Check for division by zero with IF statements
Validate inputs before complex calculations

Example with error handling:

=IFERROR(
  IF(B1=0, 0, A1/B1),
  "Calculation Error"
)

Formula Debugging¶

View Dependencies¶

To see what cells a formula depends on:

Select the cell with the formula
Click Show Dependencies in the toolbar
Dependent cells are highlighted with arrows

Evaluate Step-by-Step¶

For complex formulas, evaluate each part:

# Via API
curl -X POST https://api.calcbridge.io/api/v1/calculations/evaluate \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "formula": "=IF(SUM(A1:A5)>100, \"High\", \"Low\")",
    "context": {"A1": 10, "A2": 20, "A3": 30, "A4": 40, "A5": 50},
    "show_steps": true
  }'

Trace Errors¶

When a formula returns an error:

Click the cell with the error
View the error message in the formula bar
Check the Error Details panel for root cause
Fix the underlying issue

Examples¶

Weighted Average Calculation¶

=SUMPRODUCT(A2:A100, B2:B100) / SUM(B2:B100)

Conditional Lookup with Default¶

=IFERROR(XLOOKUP(A1, LookupTable!A:A, LookupTable!B:B), "Not Found")

Multi-Condition Count¶

=COUNTIFS(
  Rating, "CCC",
  Status, "Active",
  Amount, ">1000000"
)

Date-Based Filter¶

=SUMIFS(
  Amount:Amount,
  Date:Date, ">="&DATE(2025,1,1),
  Date:Date, "<="&DATE(2025,12,31)
)

Nested IF for Bucketing¶

=IF(A1>=90, "A",
  IF(A1>=80, "B",
    IF(A1>=70, "C",
      IF(A1>=60, "D", "F"))))

Next: Running Calculations