Excel functions in Djeeni processes

Djeeni is created for people who have built up experience and proficiency in MS Excel by using it regularly for different data processing tasks. We do want you to be able to keep and utilize your proficiency and therefore built Djeeni to seamlessly work together with MS Excel and its powerful built-in functions. In the sections below we explain through practical examples how and for what purpose you can embed Excel functions at different Djeeni process steps.

Most of the processes are not linear: they can contain repeated steps (see lists) or conditional/optional steps that must be performed only if certain conditions are met. Djeeni provides the If process step to describe the conditions.

One of the most common conditions is to check if a cell is empty. The best practice is to use the ISBLANK() Excel function as it works all possible cell type:

  If        Djeeni name: ifEmpty
            Condition: ISBLANK([$wsWorksheet!B3])      'check if cell Worksheet!B3 is empty

In MS Excel, you can set a dynamic value to a cell using a formula. Such a formula performs some calculations using a) values of one or more other cells; and b) built-in or user-defined functions. The formula is attached fixed to the cell.

In Djeeni you can keep every knowledge and experience that you have with formulas and Excel functions. Example: Let's suppose that we need to set the value of cell B4 on the worksheet wsMonthEnd by summarizing some values in the range G2:G20 on the worksheet wsERPData. In Djeeni you use the well-known SUM function with the same range parameter than in Excel within a [+….] Djeeni formula:

  Cell Set  Cell: wsMonthEnd!B4         'NB: Cells are always referred with their worksheet
            Value: [+SUM([:wsERPData!G2:G20])]  'A range is specified in [:....]

But Djeeni can do more. You can dynamically define also the cell using a Djeeni formula. Example: the sum that we gather must be set to B4 if it is less than 10000; otherwise it should go to C4:

  Cell Set  Cell: [+IF(SUM([:wsERPData!G2:G20])<10000,[$wsMonthEnd!B4],[$wsMonthEnd!C4])]  'Cell address is 
            Value: [+SUM([:wsERPData!G2:G20])]                                             'referred by [$.....]
 

This Djeeni feature is very handy when used within a workbook, row, or column list. Example: an order worksheet (wsExport) is exported from an IT system and it contains thousands of rows with different dates (column B) and product codes (column K) in a month. Based on the date and the product code, each order must be listed in three pre-defined columns per week (15 target columns altogether for the whole month).

The solution on paper:

   Go through the rows of wsExport
       Set the order value after the last row in the target column where the target column is calculated by:
            - the number of weeks from the month start using the WEEKNUM function; plus
            - the column within a week according to the product category using the IFS function

The solution in Djeeni (first temporary storing the target column calculation to cell wsTarget!ZZ1):

  Row List Start Row: 2
           End Row: #RowEnd
  Cell Set Cell: wsTarget!ZZ1                      'Temporary cell to store the target column calculation result
           Value: [+ WEEKNUM([$wsExport!B#],2)–WEEKNUM(DATE(YEAR([$wsExport!B#]),MONTH([$wsExport!B#]),1),2)+1 +
                     IFS([=wsExport!K#]<5000,0,[=wsExport!K#]<20000,1,[=wsExport!K#]>19999,2) ]
  Cell Set Cell: wsTarget![=wsTarget!ZZ1][#RowEnd|[=wsTarget!ZZ1]]
           Value: wsExport!C#
  Row List Next

If you are familiar with Pivot tables in MS Excel then this feature of Djeeni is an extension of the Pivot for more complex cases.

Working with dates is one of the most difficult tasks in MS Excel on three levels:

• There are three main date formats: Month/Day/Year; Year-Month-Day; Day-Year-Month (here we talk about writing years with 4 digits - 2-digit year notation makes the case even more difficult). Only the Year-Month-Day format is straightforward for MS Excel.
• MS Excel behaves differently according to the regional settings of the operating system. You open the same workbook (or CSV file) on two machines and one of them understands the dates while the other not, or not correctly. You cannot rely on having the date
• When dates are put in formulas they will be re-processed in MS Excel; sometimes resulting in misunderstanding the date again.

MS Excel puts a lot of undocumented effort to find and silently convert different values to dates. It makes a lot of errors: cannot find valid dates (because of the regional settings); recognizes dates where there are none; or simply misinterprets dates (e.g. makes 5th March from 3rd May) causing troubles.

Based on these issues we suggest that you use dates in Djeeni processes following these guidelines:

1. Whenever possible store every date in three separate cells: year; month; day
2. Combine the three values (let's suppose year is A1; month is B1; day is C1 on worksheet wsSheet) into a date by [+DATE([=wsSheet!A1],[=wsSheet!B1],[=wsSheet!C1])] (using the standard year-month-date order). Djeeni will use the underlying number value of the date ensuring consistency on all platforms and locales.
3. Pre-format your target cells to short date or long date to display correct date strings (instead of number values).

There is a hidden concept in MS Excel about what it means to reference a cell. When a cell (e.g. B3) is referenced in a formula or inside a function, MS Excel reads the value of the cell and uses the value further. Reading the value means also to decide if it is a number or a text or a date value (the type of the value). Certain function parameters can be only text or number or date values and MS Excel gives an error if the given value does not have the proper type. Therefore it is important to understand how Djeeni formulas treat cells and cell values when they are combined with Excel functions.

In Djeeni, cell references are always in the form wsDjeeniName!ColumnRow that can be resolved for MS Excel in two forms:

• The Djeeni cell reference is transformed into an Excel cell reference. Example: if we have a sheet1 worksheet in the workbook c:\finance\export.xlsx with Djeeni name wsExport then wsExport!A2 will be passed to Excel as '[c:\finance\export.xlsx]sheet1'!A2. To achieve this the Djeeni formula [$ … ] must be used.
• The Djeeni cell reference is processed by Djeeni by reading the cell value. Example: if the above A2 cell contains 'Toy Ship', then MS Excel will get the value 'Toy Ship' to work with. To achieve this the Djeeni formula [= … ] must be used. Djeeni does not check if the value a number or text or a date is; it is passed as a text without enclosed in “” in any case (and it is very handy e.g. when generating the dynamic part of a variable workbook name)

On the other hand, MS Excel expects “” around text values in functions. Example: If the value of the above A2 cell must be compared to 'Toy Boat' one would write in Djeeni: IF([=wsExport!A2] = “Toy Boat”,1,2); but this results in error. After processing the Djeeni formula MS Excel would see: IF(Toy Ship = “Toy Boat”,1,2).

There are two solutions:

• write: IF([$wsExport!A2] = “Toy Boat”,1,2) that will be passed to MS Excel as IF('[c:\finance\export.xlsx]sheet1'!A2 = “Toy Boat”,1,2)
• write: IF(“[=wsExport!A2]” = “Toy Boat”,1,2) that will be passed to MS Excel as IF(“Toy Ship” = “Toy Boat”,1,2)

Check always which solution fits in the formula.