Gets or sets cell formula string.

Remarks

GemBox.Spreadsheet can read and write formulas, but can not calculate formula results. When you open a XLS file in MS Excel, formula results will be calculated automaticaly.

During setting formula string GemBox.Spreadsheet formula parser will use English culture to parse numbers.

Currently supported formula features are:

• Named cell
• Named range
• Absolute cell/range
• Relative cell/range
• Functions( partly, see the list of supported functions below )
• Missed argument
• Unary operator
• Binary operator
• Parentheses
• 3d cell reference
• 3d cell range reference
• Boolean
• Integer
• Float
• String
• Error

Currently unsupported formula features are:

• Const array
• Array formula
• R1C1 reference

Currently supported functions are:

• NOW
• SECOND
• MINUTE
• HOUR
• WEEKDAY
• YEAR
• MONTH
• DAY
• TIME
• DATE
• RAND
• TEXT
• VAR
• MOD
• NOT
• OR
• AND
• FALSE
• TRUE
• VALUE
• LEN
• MID
• ROUND
• SIGN
• INT
• ABS
• LN
• EXP
• SQRT
• PI
• COS
• SIN
• COLUMN
• ROW
• MAX
• MIN
• AVERAGE
• SUM
• IF
• COUNT
• SUBTOTAL

For more information on formulas, consult Microsoft Excel documentation.

Example

Following code demonstrates how to use formulas and named ranges. It shows next features: cell references (both absolute and relative), unary and binary operators, constand operands (integer and floating point), functions and named cell ranges.

[Visual Basic]
    ws.Cells("A1").Value = 5
    ws.Cells("A2").Value = 6
    ws.Cells("A3").Value = 10
 
    ws.Cells("C1").Formula = "=A1+A2"
    ws.Cells("C2").Formula = "=$A$1-A3"
    ws.Cells("C3").Formula = "=COUNT(A1:A3)"
    ws.Cells("C4").Formula = "=AVERAGE($A$1:$A$3)"
    ws.Cells("C5").Formula = "=SUM(A1:A3,2,3)"
    ws.Cells("C7").Formula = "= 123 - (-(-(23.5)))"
 
    ws.NamedRanges.Add("DataRange", ws.Cells.GetSubrange("A1", "A3"))
    ws.Cells("C8").Formula = "=MAX(DataRange)"
 
    Dim cr As CellRange = ws.Cells.GetSubrange("B9","C10")
    cr.Merged = True
    cr.Formula = "=A1*25"

[C#]
    
    ws.Cells["A1"].Value = 5;
    ws.Cells["A2"].Value = 6;
    ws.Cells["A3"].Value = 10;

    ws.Cells["C1"].Formula = "=A1+A2";
    ws.Cells["C2"].Formula = "=$A$1-A3";
    ws.Cells["C3"].Formula = "=COUNT(A1:A3)";
    ws.Cells["C4"].Formula = "=AVERAGE($A$1:$A$3)";
    ws.Cells["C5"].Formula = "=SUM(A1:A3,2,3)";
    ws.Cells["C7"].Formula = "= 123 - (-(-(23.5)))";

    ws.NamedRanges.Add("DataRange", ws.Cells.GetSubrange("A1", "A3"));
    ws.Cells["C8"].Formula = "=MAX(DataRange)";

    CellRange cr = ws.Cells.GetSubrange("B9", "C10");
    cr.Merged = true;
    cr.Formula = "=A1*25";

See Also

ExcelCell Class | GemBox.Spreadsheet Namespace | NamedRangeCollection.Add

---