Skip to main content

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.cds dataset

Download Spreadsheet Customers.zip file click here

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

  1. Log in to OPNBI with valid credentials and create a new dashboard.

  2. From the widget library, select the spreadsheet widget to add to the dashboard. You will see the widget properties window.

    Docusaurus Slash Introduction

  3. Select the dataset you want to view and analyze, To demonstrate, we are using the Customer.cds dataset.

    Docusaurus Slash Introduction

  4. Click the save and exit button and see the Customers.cds dataset in the spreadsheet widget.

  5. Resize the widget to view it properly, as shown in the figure below:

Docusaurus Slash Introduction

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 Credit Limit Column from cell j2 to j10.

  3. For that write the formulas =AVERAGE(J2:J10) displays the average of Credit Limit column cells 2 to 10, as shown in the figure below:

Docusaurus Slash Introduction

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

Docusaurus Slash Introduction

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. Click the cell where you want to add a formula.

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

Docusaurus Slash Introduction

  1. Click on Insert Function to bring up the Insert Function dialog box, as shown in the figure below:

Docusaurus Slash Introduction

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

Docusaurus Slash Introduction

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

Docusaurus Slash Introduction

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

Docusaurus Slash Introduction

  1. Click on File >> Save.