Formulas
Master Excel formulas - cell references, built-in functions, and complex calculations
Learn how to add formulas and calculations to your spreadsheets using the BoxLang Fluent API. This guide covers everything from basic arithmetic to complex Excel functions.
📝 Formula Basics
Adding Simple Formulas
Spreadsheet( "calculations.xlsx" )
.setRowData( 1, [ "Price", "Quantity", "Total" ] )
.setCellValue( 2, 1, 29.99 )
.setCellValue( 2, 2, 5 )
// Add formula
.setCellFormula( 2, 3, "A2*B2" )
.save();Reading Formulas
sheet = Spreadsheet( "with-formulas.xlsx", load = true );
// Get formula text
formula = sheet.getCellFormula( 2, 3 ); // Returns: "A2*B2"
// Get calculated value
value = sheet.getCellValue( 2, 3 ); // Returns: 149.95
// Get cell type
type = sheet.getCellType( 2, 3 ); // Returns: "formula"Recalculating Formulas
sheet = Spreadsheet( "formulas.xlsx", load = true )
// Update source data
.setCellValue( 2, 1, 39.99 )
// Force recalculation
.recalculateAllFormulas()
.save();🔤 Cell References
Relative References
Spreadsheet( "relative.xlsx" )
.setRowData( 1, [ "Item", "Price", "Qty", "Total" ] )
.addRow( [ "Widget", 29.99, 5 ] )
.addRow( [ "Gadget", 49.99, 3 ] )
.addRow( [ "Gizmo", 19.99, 10 ] )
// Relative references - adjust when copied
.setCellFormula( 2, 4, "B2*C2" )
.setCellFormula( 3, 4, "B3*C3" )
.setCellFormula( 4, 4, "B4*C4" )
.save();Absolute References
Spreadsheet( "absolute.xlsx" )
.setRowData( 1, [ "Tax Rate", 0.08 ] )
.setRowData( 2, [ "Item", "Price", "Tax", "Total" ] )
.addRow( [ "Widget", 100 ] )
.addRow( [ "Gadget", 200 ] )
// Absolute reference to tax rate ($B$1)
.setCellFormula( 3, 3, "B3*$B$1" )
.setCellFormula( 4, 3, "B4*$B$1" )
// Total with absolute reference
.setCellFormula( 3, 4, "B3+(B3*$B$1)" )
.setCellFormula( 4, 4, "B4+(B4*$B$1)" )
.save();Mixed References
Spreadsheet( "mixed.xlsx" )
.setRowData( 1, [ "Multiplier", 2, 3, 4 ] )
.setRowData( 2, [ "Value", 10 ] )
.setRowData( 3, [ "Value", 20 ] )
// Mixed references: $B2 (column fixed), B$1 (row fixed)
.setCellFormula( 2, 2, "$B2*B$1" ) // 10 * 2 = 20
.setCellFormula( 2, 3, "$B2*C$1" ) // 10 * 3 = 30
.setCellFormula( 2, 4, "$B2*D$1" ) // 10 * 4 = 40
.save();Range References
Spreadsheet( "ranges.xlsx" )
.setRowData( 1, [ "Values" ] )
.addRow( [ 10 ] )
.addRow( [ 20 ] )
.addRow( [ 30 ] )
.addRow( [ 40 ] )
.setRowData( 6, [ "Sum", "=SUM(A2:A5)" ] )
.setRowData( 7, [ "Average", "=AVERAGE(A2:A5)" ] )
.setRowData( 8, [ "Max", "=MAX(A2:A5)" ] )
.setRowData( 9, [ "Min", "=MIN(A2:A5)" ] )
.save();➕ Arithmetic Operations
Basic Operations
Spreadsheet( "arithmetic.xlsx" )
.setRowData( 1, [ "Operation", "Formula", "Result" ] )
// Addition
.setRowData( 2, [ "Addition", "=10+5" ] )
// Subtraction
.setRowData( 3, [ "Subtraction", "=10-5" ] )
// Multiplication
.setRowData( 4, [ "Multiplication", "=10*5" ] )
// Division
.setRowData( 5, [ "Division", "=10/5" ] )
// Exponentiation
.setRowData( 6, [ "Power", "=10^2" ] )
// Modulo
.setRowData( 7, [ "Modulo", "=10%3" ] )
.save();Order of Operations
Spreadsheet( "order.xlsx" )
.setRowData( 1, [ "Expression", "Result" ] )
// Without parentheses
.setRowData( 2, [ "=2+3*4", "=2+3*4" ] ) // 14 (multiplication first)
// With parentheses
.setRowData( 3, [ "=(2+3)*4", "=(2+3)*4" ] ) // 20 (addition first)
.save();📊 Common Excel Functions
SUM Functions
Spreadsheet( "sum.xlsx" )
.setRowData( 1, [ "Jan", "Feb", "Mar", "Total" ] )
.addRow( [ 100, 200, 150 ] )
// Simple SUM
.setCellFormula( 2, 4, "SUM(A2:C2)" )
// SUM with multiple ranges
.setRowData( 3, [ 50, 75, 60 ] )
.setCellFormula( 3, 4, "SUM(A3:C3)" )
.setRowData( 4, [ "Total", "", "", "=SUM(D2:D3)" ] )
.save();AVERAGE, MIN, MAX
Spreadsheet( "stats.xlsx" )
.setRowData( 1, [ "Values" ] )
.addRow( [ 85 ] )
.addRow( [ 92 ] )
.addRow( [ 78 ] )
.addRow( [ 95 ] )
.addRow( [ 88 ] )
.setRowData( 7, [ "Average", "=AVERAGE(A2:A6)" ] )
.setRowData( 8, [ "Minimum", "=MIN(A2:A6)" ] )
.setRowData( 9, [ "Maximum", "=MAX(A2:A6)" ] )
.setRowData( 10, [ "Count", "=COUNT(A2:A6)" ] )
.save();COUNT Functions
Spreadsheet( "counting.xlsx" )
.setRowData( 1, [ "Data" ] )
.addRow( [ 10 ] )
.addRow( [ "" ] )
.addRow( [ 20 ] )
.addRow( [ "Text" ] )
.addRow( [ 30 ] )
// COUNT - counts numbers only
.setRowData( 7, [ "Numbers", "=COUNT(A2:A6)" ] ) // 3
// COUNTA - counts non-empty cells
.setRowData( 8, [ "Non-Empty", "=COUNTA(A2:A6)" ] ) // 4
// COUNTBLANK - counts empty cells
.setRowData( 9, [ "Empty", "=COUNTBLANK(A2:A6)" ] ) // 1
.save();IF Function
Spreadsheet( "if-function.xlsx" )
.setRowData( 1, [ "Score", "Grade" ] )
.addRow( [ 95 ] )
.addRow( [ 82 ] )
.addRow( [ 67 ] )
.addRow( [ 55 ] )
// Simple IF
.setCellFormula( 2, 2, 'IF(A2>=90,"A","B")' )
// Nested IF
.setCellFormula( 3, 2, 'IF(A3>=90,"A",IF(A3>=80,"B","C"))' )
.setCellFormula( 4, 2, 'IF(A4>=90,"A",IF(A4>=80,"B",IF(A4>=70,"C","F")))' )
.setCellFormula( 5, 2, 'IF(A5>=90,"A",IF(A5>=80,"B",IF(A5>=70,"C","F")))' )
.save();SUMIF and COUNTIF
Spreadsheet( "conditional.xlsx" )
.setRowData( 1, [ "Product", "Sales", "Region" ] )
.addRow( [ "Widget", 100, "North" ] )
.addRow( [ "Gadget", 200, "South" ] )
.addRow( [ "Widget", 150, "North" ] )
.addRow( [ "Gizmo", 175, "North" ] )
.addRow( [ "Widget", 125, "South" ] )
// SUMIF - sum where condition is met
.setRowData( 7, [ "Widget Total", '=SUMIF(A2:A6,"Widget",B2:B6)' ] )
// COUNTIF - count where condition is met
.setRowData( 8, [ "Widget Count", '=COUNTIF(A2:A6,"Widget")' ] )
// SUMIF with comparison
.setRowData( 9, [ "Sales > 150", "=SUMIF(B2:B6,"">150"",B2:B6)" ] )
.save();VLOOKUP
Spreadsheet( "vlookup.xlsx" );
// Lookup table
sheet.createSheet( "Products" )
.setRowData( 1, [ "ID", "Name", "Price" ] )
.addRow( [ 1, "Widget", 29.99 ] )
.addRow( [ 2, "Gadget", 49.99 ] )
.addRow( [ 3, "Gizmo", 19.99 ] );
// Order sheet with lookups
sheet.createAndSelectSheet( "Orders" )
.setRowData( 1, [ "Product ID", "Product Name", "Price", "Quantity", "Total" ] )
.addRow( [ 2 ] )
.addRow( [ 1 ] )
.addRow( [ 3 ] );
// VLOOKUP formulas
// Lookup name
sheet.setCellFormula( 2, 2, "VLOOKUP(A2,Products!A:C,2,FALSE)" )
// Lookup price
.setCellFormula( 2, 3, "VLOOKUP(A2,Products!A:C,3,FALSE)" )
// Set quantity
.setCellValue( 2, 4, 5 )
// Calculate total
.setCellFormula( 2, 5, "C2*D2" );
// Repeat for other rows
sheet.setCellFormula( 3, 2, "VLOOKUP(A3,Products!A:C,2,FALSE)" )
.setCellFormula( 3, 3, "VLOOKUP(A3,Products!A:C,3,FALSE)" )
.setCellValue( 3, 4, 3 )
.setCellFormula( 3, 5, "C3*D3" );
sheet.save();TEXT Functions
Spreadsheet( "text-functions.xlsx" )
.setRowData( 1, [ "Text", "Upper", "Lower", "Length", "Concatenate" ] )
.addRow( [ "Hello World" ] )
// UPPER - convert to uppercase
.setCellFormula( 2, 2, "UPPER(A2)" )
// LOWER - convert to lowercase
.setCellFormula( 2, 3, "LOWER(A2)" )
// LEN - get length
.setCellFormula( 2, 4, "LEN(A2)" )
// CONCATENATE or & operator
.setCellFormula( 2, 5, 'CONCATENATE(A2," - ","Modified")' )
.save();DATE Functions
Spreadsheet( "date-functions.xlsx" )
.setRowData( 1, [ "Function", "Result" ] )
// TODAY - current date
.setRowData( 2, [ "Today", "=TODAY()" ] )
// NOW - current date and time
.setRowData( 3, [ "Now", "=NOW()" ] )
// DATE - create specific date
.setRowData( 4, [ "Custom Date", "=DATE(2024,12,25)" ] )
// YEAR, MONTH, DAY
.setRowData( 5, [ "Year", "=YEAR(TODAY())" ] )
.setRowData( 6, [ "Month", "=MONTH(TODAY())" ] )
.setRowData( 7, [ "Day", "=DAY(TODAY())" ] )
// Date arithmetic
.setRowData( 8, [ "30 Days Later", "=TODAY()+30" ] )
.setRowData( 9, [ "Days Between", "=DATE(2024,12,31)-TODAY()" ] )
.save();🔗 Cross-Sheet Formulas
Referencing Other Sheets
sheet = Spreadsheet( "cross-sheet.xlsx" );
// Data sheet
sheet.createSheet( "Data" )
.setRowData( 1, [ "Revenue", 100000 ] )
.setRowData( 2, [ "Expenses", 75000 ] );
// Summary sheet with formulas
sheet.createAndSelectSheet( "Summary" )
.setRowData( 1, [ "Revenue", "=Data!B1" ] )
.setRowData( 2, [ "Expenses", "=Data!B2" ] )
.setRowData( 3, [ "Profit", "=B1-B2" ] );
sheet.save();Complex Cross-Sheet Calculations
sheet = Spreadsheet( "quarterly.xlsx" );
// Q1 Data
sheet.createSheet( "Q1" )
.setRowData( 1, [ "Month", "Sales" ] )
.addRow( [ "Jan", 10000 ] )
.addRow( [ "Feb", 12000 ] )
.addRow( [ "Mar", 11000 ] );
// Q2 Data
sheet.createSheet( "Q2" )
.setRowData( 1, [ "Month", "Sales" ] )
.addRow( [ "Apr", 13000 ] )
.addRow( [ "May", 14000 ] )
.addRow( [ "Jun", 12500 ] );
// Summary with cross-sheet formulas
sheet.createAndSelectSheet( "Summary" )
.setRowData( 1, [ "Quarter", "Total Sales" ] )
.setRowData( 2, [ "Q1", "=SUM(Q1!B2:B4)" ] )
.setRowData( 3, [ "Q2", "=SUM(Q2!B2:B4)" ] )
.setRowData( 4, [ "Total", "=SUM(B2:B3)" ] );
sheet.save();🎯 Formula Patterns
Running Totals
Spreadsheet( "running-total.xlsx" )
.setRowData( 1, [ "Date", "Amount", "Running Total" ] )
.addRow( [ "2024-01-01", 100 ] )
.addRow( [ "2024-01-02", 150 ] )
.addRow( [ "2024-01-03", 75 ] )
.addRow( [ "2024-01-04", 200 ] )
// First running total
.setCellFormula( 2, 3, "B2" )
// Subsequent running totals
.setCellFormula( 3, 3, "C2+B3" )
.setCellFormula( 4, 3, "C3+B4" )
.setCellFormula( 5, 3, "C4+B5" )
.save();Percentage Calculations
Spreadsheet( "percentages.xlsx" )
.setRowData( 1, [ "Product", "Sales", "% of Total" ] )
.addRow( [ "Widget", 1000 ] )
.addRow( [ "Gadget", 1500 ] )
.addRow( [ "Gizmo", 500 ] )
.setRowData( 5, [ "Total", "=SUM(B2:B4)" ] )
// Percentage of total (with absolute reference to total)
.setCellFormula( 2, 3, "B2/$B$5" )
.setCellFormula( 3, 3, "B3/$B$5" )
.setCellFormula( 4, 3, "B4/$B$5" )
// Format as percentage
.formatColumn( 3, { dataformat: "0.0%" } )
.save();Variance Analysis
Spreadsheet( "variance.xlsx" )
.setRowData( 1, [ "Item", "Budget", "Actual", "Variance", "% Variance" ] )
.addRow( [ "Revenue", 100000, 105000 ] )
.addRow( [ "Salaries", 50000, 52000 ] )
.addRow( [ "Marketing", 10000, 9500 ] )
// Variance (Actual - Budget)
.setCellFormula( 2, 4, "C2-B2" )
.setCellFormula( 3, 4, "C3-B3" )
.setCellFormula( 4, 4, "C4-B4" )
// % Variance
.setCellFormula( 2, 5, "D2/B2" )
.setCellFormula( 3, 5, "D3/B3" )
.setCellFormula( 4, 5, "D4/B4" )
// Format
.formatColumn( 5, { dataformat: "0.0%" } )
.formatColumns( "2-4", { dataformat: "$#,##0" } )
.save();Ranking
Spreadsheet( "ranking.xlsx" )
.setRowData( 1, [ "Student", "Score", "Rank" ] )
.addRow( [ "John", 95 ] )
.addRow( [ "Jane", 88 ] )
.addRow( [ "Bob", 92 ] )
.addRow( [ "Alice", 97 ] )
// RANK function
.setCellFormula( 2, 3, "RANK(B2,$B$2:$B$5)" )
.setCellFormula( 3, 3, "RANK(B3,$B$2:$B$5)" )
.setCellFormula( 4, 3, "RANK(B4,$B$2:$B$5)" )
.setCellFormula( 5, 3, "RANK(B5,$B$2:$B$5)" )
.save();🔧 Formula Helpers
Using BoxLang to Generate Formulas
// Generate SUM formulas for multiple rows
sheet = Spreadsheet( "generated.xlsx" )
.setRowData( 1, [ "Q1", "Q2", "Q3", "Q4", "Total" ] );
// Add data rows
for ( i = 1; i <= 5; i++ ) {
row = [
randRange( 1000, 2000 ),
randRange( 1000, 2000 ),
randRange( 1000, 2000 ),
randRange( 1000, 2000 )
];
sheet.addRow( row );
// Add SUM formula
rowNum = i + 1;
sheet.setCellFormula( rowNum, 5, "SUM(A#rowNum#:D#rowNum#)" );
}
sheet.save();Dynamic Range Formulas
// Create formula with dynamic range based on data
data = [
[ "Product A", 100 ],
[ "Product B", 200 ],
[ "Product C", 150 ]
];
sheet = Spreadsheet( "dynamic.xlsx" )
.setRowData( 1, [ "Product", "Sales" ] )
.addRows( data );
// Calculate last row
lastRow = data.len() + 1;
// Add total with dynamic range
sheet.setRowData( lastRow + 1, [ "Total", "=SUM(B2:B#lastRow#)" ] );
sheet.save();⚠️ Formula Tips
String Escaping in Formulas
When formulas contain quotes, use proper escaping:
// Use single quotes inside double-quoted strings
sheet.setCellFormula( 1, 1, 'IF(A1>10,"Pass","Fail")' );
// Or escape double quotes
sheet.setCellFormula( 1, 1, "IF(A1>10,""Pass"",""Fail"")" );Error Handling
Spreadsheet( "error-handling.xlsx" )
.setRowData( 1, [ "Value", "Divided by", "Result" ] )
.addRow( [ 100, 5 ] )
.addRow( [ 100, 0 ] ) // Will cause #DIV/0! error
// Without error handling
.setCellFormula( 2, 3, "A2/B2" ) // Works
.setCellFormula( 3, 3, "A3/B3" ) // #DIV/0! error
// With IFERROR
.setCellFormula( 4, 3, 'IFERROR(A3/B3,"Cannot divide by zero")' )
.save();Array Formulas
Some Excel formulas work with arrays:
Spreadsheet( "arrays.xlsx" )
.setRowData( 1, [ "Values", "Squared" ] )
.addRow( [ 2 ] )
.addRow( [ 3 ] )
.addRow( [ 4 ] )
// Individual formulas
.setCellFormula( 2, 2, "A2^2" )
.setCellFormula( 3, 2, "A3^2" )
.setCellFormula( 4, 2, "A4^2" )
.save();📚 Next Steps
Advanced FeaturesExampleshttps://github.com/ortus-boxlang/boxlang-docs/blob/v1.x/boxlang-framework/boxlang-plus/modules/bx-spreadsheet/reference/fluent-api/README.mdLast updated
Was this helpful?