Introduction
In an Excel Worksheet, we may import a great deal of data. Sometimes, we need to calculate the data to get another numbers we need. Formula is the mail tool to calculate data.
There are various formulas included in Excel. Actually, formulas are equations and each one starts with an equal sign. We can use a formula to calculate values for a column list. A formula contains four parts: functions, column references, operators and constants.
How to Add Formula for Excel in MS
There are different methods to add a formula for Excel including different part. This example is about formulas containing functions. The example is about how to average all number in the range from A1 to B4.
Firstly, click the cell where we wan to add the formula. Then, click Insert Function on the formula bar. Thirdly, select the function we want to use. We can search the function or browse form the categories. Next, enter the arguments. Click Collapse Dialog to hide the dialog box to enter cell references as an argument. Press Expand Dialog after selecting the cells on the worksheet. Finally, Press Enter when we complete the formula.
How to Add Formula for Excel with Spire.XLS
Spire.XLS presents you an easiest way to add formula for Excel. We will give you a demo with many kinds of formulas written in the worksheet. The formula can be string, bool value, calculation, sheet area reference, time and so on. Here, we reference a variable "currentRow" to control rows of all kinds of formulas. You may add formula text with the property of sheet.Range[++currentRow, 1].Text, and calculate the formula with the property of sheet.Range[currentRow, 2].Formula.
The following code shows how to add a formula for Excel with C#/VB.NET:
[C#]
view sourceprint?
01 using Spire.Xls;
02
03 namespace Formula
04 {
05     class Program
06     {
07         static void Main(string[] args)
08         {
09             //Create a new workbook
10             Workbook workbook = new Workbook();
11
12             //Initialize worksheet
13             Worksheet sheet = workbook.Worksheets[0];
14
15             //initialize currentRow
16             int currentRow = 3;
17             string currentFormula = string.Empty;
18             
19             //test data
20             sheet.Range[currentRow, 2].NumberValue = 7.3;
21             sheet.Range[currentRow, 3].NumberValue = 5; ;
22             sheet.Range[currentRow, 4].NumberValue = 8.2;
23             sheet.Range[currentRow, 5].NumberValue = 4;
24             sheet.Range[currentRow, 6].NumberValue = 3;
25             sheet.Range[currentRow, 7].NumberValue = 11.3;
26             
27             //string.
28             currentFormula = "=\"hello\"";
29             sheet.Range[++currentRow, 1].Text = "=\"hello\"";
30             sheet.Range[currentRow, 2].Formula = currentFormula;
31
32             //bool.
33             currentFormula = "=false";
34             sheet.Range[++currentRow, 1].Text = currentFormula;
35             sheet.Range[currentRow, 2].Formula = currentFormula;
36
37             //calculation
38             currentFormula = "=33*3/4-2+10";
39             sheet.Range[++currentRow, 1].Text = currentFormula;
40             sheet.Range[currentRow, 2].Formula = currentFormula;
41
42             //sheet area reference
43             currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)";
44             sheet.Range[++currentRow, 1].Text = currentFormula;
45             sheet.Range[currentRow, 2].Formula = currentFormula;
46
47             //time
48             currentFormula = "=NOW()";
49             sheet.Range[++currentRow, 1].Text = currentFormula;
50             sheet.Range[currentRow, 2].Formula = currentFormula;
51             sheet.Range[currentRow, 2].Style.NumberFormat = "yyyy-MM-DD";
52
53             //Save the file
54             workbook.SaveToFile("Sample.xls");
55
56             //Launch the file
57             System.Diagnostics.Process.Start("Sample.xls");
58         }
59     }
60 }
[Visual Basic]
view sourceprint?
01 Imports Spire.Xls
02
03 Module Module1
04
05     Sub Main()
06         'Create a new workbook
07         Dim workbook As New Workbook()
08
09         'Initialize worksheet
10         Dim sheet As Worksheet = workbook.Worksheets(0)
11
12         'initialize currentRow
13         Dim currentRow As Integer = 3
14         Dim currentFormula As String = String.Empty
15
16         'test data
17         sheet.Range(currentRow, 2).NumberValue = 7.3
18         sheet.Range(currentRow, 3).NumberValue = 5
19
20
21         sheet.Range(currentRow, 4).NumberValue = 8.2
22         sheet.Range(currentRow, 5).NumberValue = 4
23         sheet.Range(currentRow, 6).NumberValue = 3
24         sheet.Range(currentRow, 7).NumberValue = 11.3
25
26         'string.
27         currentFormula = "=""hello"""
28         sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Text = "=""hello"""
29         sheet.Range(currentRow, 2).Formula = currentFormula
30
31         'bool.
32         currentFormula = "=false"
33         sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Text = currentFormula
34         sheet.Range(currentRow, 2).Formula = currentFormula
35
36         'calculation
37         currentFormula = "=33*3/4-2+10"
38         sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Text = currentFormula
39         sheet.Range(currentRow, 2).Formula = currentFormula
40
41         'sheet area reference
42         currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)"
43         sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Text = currentFormula
44         sheet.Range(currentRow, 2).Formula = currentFormula
45
46         'time
47         currentFormula = "=NOW()"
48         sheet.Range(System.Threading.Interlocked.Increment(currentRow), 1).Text = currentFormula
49         sheet.Range(currentRow, 2).Formula = currentFormula
50         sheet.Range(currentRow, 2).Style.NumberFormat = "yyyy-MM-DD"
51
52
53         'Save doc file.
54         workbook.SaveToFile("Sample.xls")
55
56         'Launching the MS Word file.
57         System.Diagnostics.Process.Start("Sample.xls")
58
59     End Sub
60 End Module

http://www.e-iceblue.com/

[Edited by Daisy09 on 15-02-2011 at 08:08 AM GMT]