Formulas

Introduction

Formulas are used for calculating the data in a worksheet. You can refer to the cell reference from the same sheet or different sheets. The formula bar is used to edit or enter cell data in a much easier way. By default, the formula bar is enabled in the spreadsheet.

You can set a formula using the formula property from the cell, you can set the formula or expression to each cell at the initial load. The list of formulas supported in the spreadsheet is sufficient for most of your calculations.

Prerequisite

Users will require the following dataset to understand edit spreadsheet scenarios: Customers.ds dataset

Download Spreadsheet Customers.zip file click

Permissions

For the spreadsheet widget, certain permissions need to be set. Before adding any formulas to the spreadsheet, ensure you set the following permissions on the widget:

• Uncheck “Allow Protection”
• Check “Show Ribbon”
• Check “Show Formula Bar”
• Check “Allow Editing”

To use the Formulas in the Spreadsheet follow the steps below:

1. Select the dataset you want to view and analyze, To demonstrate, we are using the Customers.ds dataset.

2. Click the publish button and validate the data from the spreadsheet widget.
3. Resize the widget to view it properly, as shown in the figure below:

Typing a formula inside the cell

Typing a formula in a cell or the formula bar is the most straightforward method of inserting Excel formulas. The process usually starts by typing an equal sign, followed by the name of an Excel function. For demonstration, we are using Average.

1. On the spreadsheet, click the cell in which you want to enter the formula.
2. For demonstration we are selecting the Average formula in the SALESREPEMPLOYEENUMBER Column from cell L2 to L10.
3. For that write the formulas =AVERAGE(L2:L10) displays the average of SALESREPEMPLOYEENUMBER column cells 2 to 11, as shown in the figure below:

4. Hit enter and the result of the calculation appears in the cell.

Insert Function

Insert Function command lets you search for the function you want and also guides you through inserting the arguments, which is helpful for complex functions.

1. To fetch the formulas properties on spreadsheet, enable the formula bar by clicking on the Show FormulaBar checkbox from editmenu.

2. Then Click the cell where you want to add a formula.

3. Go to the Formulas >> Insert Function, as shown in the figure below:

4. Click on Insert Function to bring up the Insert Function dialog box, as shown in the figure above.

5. Search for a function or select a function from a category. For example, choose COUNTIF from the category, as shown in the figure below:

6. Write the function =COUNTIF(L2:L10, ">1200") the COUNTIF function counts the Credit Limit of cells that are greater than 1200, as shown in the figure below:

7. Hit enters to see Output, as shown in the figure below:

8. Click on File >> Save button.