Define Function

A function is a predefined formula that performs calculations using specific values in a particular order. Such as Sum of numbers it will take the values from cells and perform addition operation and return some value.

Any chain of calculations made up of inline functions as well as output functions can be encapsulated into a custom function. This is achieved using DefineFunction. The arguments to DefineFunction specify which cells are to be treated as inputs and which cells are to be treated as the results/outputs. Intermediate cells are not specified. When Run is clicked on a workbook containing one or more calls to DefineFunction, SheetKraft determines the set of cells that make up the calculation chain for each custom function. The values and formulas of these cells are captured and a function definition is registered dynamically with Excel. This makes the function available in Excel for use in formulas. The captured function can even be exported into a library. If this is done, the function is usable in Excel even when the workbook containing its definition is not open.

This example shows how to define a custom function using SheetKraft's Define Function tool.

So you can define a function which can be used multiple times with the help of SK.

A simple example can be the sum of squares of two numbers. Use the define function from the SK ribbon. Open the excel file attached.

Note-

1) The function name cannot be pre-defined function name.

2) You should use region down if you want to call the function for multiple inputs.

Example 1:

Exporting settlement data to segregate settlement data for each merchant in different files. Each file will contain data on different payment method displayed in separate sheets

Step 1:

We have filtered the data according to the merchant name on a different sheet.

Step 2:

We then export the filtered data into multiple sheets partitioning by payment mode (X11) in Defining Function Sheet.

Step 3:

The function is defined to export partitioned data for a single merchant. we can use this function to get output for all the merchants.

Step 4:

So while defining we only have to define the inputs and the outputs and no process in between SK will understand all the process involved in between. So here our input is Merchant name and output is export to excel (X11) in Defining Function Sheet.

Note: The data in the input file can change and the same merchant name might not be present in the new batch of data. This will give us an error while defining function as the merchant name is not present.

So, for this purpose we have created all the different merchant names present in the report and provided them with Sr No. and while defining we have used Sr No instead of Merchant name where each number is linked with a particular Merchant name.

This will ensure the serial number will always be present even if the Merchant Name changes in the next batch of data and our define function will always work. The Serial Number and Merchant name will always work in combination even if data changes.

Please look at how the value in the sheet changes if you change the value in A2 of Defining Function Sheet.

Step 5:

We have defined the function. (B6)

  • Go to define function from the UI

  • Refer the cells that are the Inputs here we have taken (A2)

  • Refer the cell that have results(X11) in Results

Once a function is defined you can use it like various other function of excel.

Step 6:

We can call the function and input the Sr No. (Assigned to a particular merchant name) to get the output for that merchant. (E2) in Single Merchant sheet

Step 7:

We have repeated the export for various merchants present in the report by using region down of all the merchants present in the file. This will create multiple files repeatedly for all the merchant names and splitting the data on the basis of payment mode.

Example 2:

Filtering by Threshold Amount. The function filters data set with a specific criterion.

We have a data(shown in below figure) and we need to filter it by certain defined Threshold_amount(G1). We define the condition at F3 as =D3>$G$1 with relative referencing on D3 and absolute on G1 as we want to filter it by G1. We Hit enter and obtain False.

Now we want to define a custom function for all the operation we performed above.

Step 1: To do this, go to SheetKraft >> DefineFunction.

Step 2: Write the Function Name and the Description in the corresponding dialog boxes.

Now we need to add the Functions arguments to our function. From our operation performed above, one argument was Amount at D3. We define as many numbers of arguments by clicking on Add. But in our operation, we used only two arguments. Define it by clicking on Add and selecting the cell containing the argument G1.

In our operation, the result was made in F3. So in the Result dialog box, write the corresponding cell name. You can select the cell using the arrow tool provided aside the result dialog box.

Once all arguments and the result is written, hit Accept.

Step 3: Now our Custom function Filter_by_threshold_amount has been defined. We can use it just like other excel functions.

Step 4: This makes the function available in Excel for use in formulas. The captured function can even be exported into a library. If this is done, the function is usable in Excel even when the workbook containing its definition is not open.