Adding a Formula Column

Version

Application Scenario

Using formula functions to obtain new fields by calculating the original fields without affecting the original fields.

Function Description

You can calculate by using basic arithmetic operations, common functions, numeric functions, date functions, text functions, logic functions, and other functions.

You can download the sample data: Contract Information.xlsx.

You can add the Contract Sum and Purchase Quantity fields to a formula to calculate the average price of each contract.

1. Create an analysis subject and upload the sample data Contract Information. Go to the data editing page.

2. Add Formula Column (two methods), as shown in the following figure.

3. Select a formula/function. Use the formula Contract Sum/Purchase Quantity to create a field named Average Price. Select the field type as Value, as shown in the following figure.

Note:

1. The value of the field type is Auto by default. In this case, the system automatically judges the field type based on the generated results.

2. The added fields need to be selected from the left column.

4. In the formula input box:

You can search for fields or functions by entering the first letter or first word.

You can click functions or fields on the left to add them to the input box.

5. Click OK. You can see the newly added data column Average Price in the data preview box.

In addition to using simple basic arithmetic operations, you can also add the formula column with functions.

The example background: For instance, a contract is defined as Major Contract if its monetary value exceeds 1,000,000, otherwise, the contract is defined as Minor Contract.

The implementation approach: You need to use IF Function as the judgment condition, add a formula column, and enter the formula.

Procedure:

1. Create an analysis subject and upload the sample data Contract Information. Go to the data editing page.

2. Add Formula Column.

3. Name the newly added formula column as Contract Size Labeling. Enter the formula: IF(Contract Sum>1000000,"Major Contract","Minor Contract"), as shown in the following figure.

4. Click OK. You can see the Contract Size Labeling field, as shown in the following figure.