# Formula Utility Methods

Following sample demonstrates some GemBox.Spreadsheet utility methods that can be useful for creating formulas.

Screenshot

See the full code below, use RUN EXAMPLE to execute.

``````1using System;
2using System.Collections.Generic;
3using System.Text;
7
8class Sample
9{
11    static void Main(string[] args)
12    {
13        // If using Professional version, put your serial key below.
15
16        ExcelFile ef = new ExcelFile();
17        ExcelWorksheet ws = ef.Worksheets.Add("Formula Utility Methods");
18
19        // Fill first column with values.
20        for (int i = 0; i < 10; i++)
21            ws.Cells[i, 0].Value = i + 1;
22
23        // Cell B1 has formula '=A1*2', B2 '=A2*2', etc.
24        for (int i = 0; i < 10; i++)
25            ws.Cells[i, 1].Formula = String.Format("={0}*2", CellRange.RowColumnToPosition(i, 0));
26
27        // Cell C1 has formula '=SUM(A1:B1)', C2 '=SUM(A2:B2)', etc.
28        for (int i = 0; i < 10; i++)
29            ws.Cells[i, 2].Formula = String.Format("=SUM(A{0}:B{0})", ExcelRowCollection.RowIndexToName(i));
30
31        // Cell A12 contains sum of all values from the first row.
32        ws.Cells["A12"].Formula = String.Format("=SUM(A1:{0}1)", ExcelColumnCollection.ColumnIndexToName(ws.Rows[0].AllocatedCells.Count - 1));
33
34        ef.Save("Formula Utility Methods.xls");
35    }
36}
``````
``````1Imports System
2Imports System.Collections.Generic
3Imports System.Text
7
8Module Samples
9
10    Sub Main()
11
12        ' If using Professional version, put your serial key below.
14
15        Dim ef As ExcelFile = New ExcelFile
16        Dim ws As ExcelWorksheet = ef.Worksheets.Add("Formula Utility Methods")
17
18        ' Fill first column with values.
19        Dim i As Int32
20        For i = 0 To 9 Step 1
21            ws.Cells(i, 0).Value = i + 1
22        Next
23
24        ' Cell B1 has formula '=A1*2', B2 '=A2*2', etc.
25        For i = 0 To 9 Step 1
26            ws.Cells(i, 1).Formula = String.Format("={0}*2", CellRange.RowColumnToPosition(i, 0))
27        Next
28
29        ' Cell C1 has formula '=SUM(A1:B1)', C2 '=SUM(A2:B2)', etc.
30        For i = 0 To 9 Step 1
31            ws.Cells(i, 2).Formula = String.Format("=SUM(A{0}:B{0})", ExcelRowCollection.RowIndexToName(i))
32        Next
33
34        ' Cell A12 contains sum of all values from the first row.
35        ws.Cells("A12").Formula = String.Format("=SUM(A1:{0}1)", ExcelColumnCollection.ColumnIndexToName(ws.Rows(0).AllocatedCells.Count - 1))
36
37        ef.Save("Formula Utility Methods.xls")
38
39    End Sub
40
41End Module
``````

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.