Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
en:user_guide:djeeniformula [2021/04/26 22:35] tiger [Create dynamic filenames and text values] |
en:user_guide:djeeniformula [2022/10/25 14:22] (current) tiger [Dates in formulas] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| {{indexmenu_n> | {{indexmenu_n> | ||
| - | ====== | + | ====== Using Djeeni Formulas ====== |
| In the end, MS Excel data sit in the cells within worksheets. In this chapter, you can learn in detail how to | In the end, MS Excel data sit in the cells within worksheets. In this chapter, you can learn in detail how to | ||
| Line 10: | Line 10: | ||
| ===== Access data in cells ===== | ===== Access data in cells ===== | ||
| - | The basic access of a cell in Djeeni (just like in MS Excel) is specifying the worksheet name followed by the exclamation mark, column letter and row number: | + | The basic access of a cell in Djeeni (just like in MS Excel) is specifying the worksheet name followed by the exclamation mark, column letter, and row number: |
| < | < | ||
| Line 17: | Line 17: | ||
| where the worksheet is identified by its [[en: | where the worksheet is identified by its [[en: | ||
| - | A range is also the same as in MS Excel: two cells on the same worksheet separated by colon. | + | A range is also the same as in MS Excel: two cells on the same worksheet |
| < | < | ||
| Line 77: | Line 77: | ||
| < | < | ||
| - | =SUM([: | + | =SUM([: |
| </ | </ | ||
| - | where [....] denotes the Djeeni formula part. If an MS Excel function has multiple parameters, all of them can get a value using Djeeni formulas: | + | where **[....]** denotes the Djeeni formula part. If an MS Excel function has multiple parameters, all of them can get a value using Djeeni formulas: |
| < | < | ||
| =IF([$wsMaster!B# | =IF([$wsMaster!B# | ||
| + | </ | ||
| + | |||
| + | And if you need to use an MS Excel formula inside of a process parameter value then you can use **[+...]**: | ||
| + | |||
| + | < | ||
| + | WSheet Use Filename: Report-[+TEXT(TODAY()," | ||
| </ | </ | ||
| Line 96: | Line 102: | ||
| </ | </ | ||
| + | **Note**: The difference between [$...] and [=...] is very important: | ||
| + | * [$....] is translated by Djeeni to MS Excel syntax and left to MS Excel to read the actual value: [$wsReport!A4] will be (let's say that wsReport is located at **c: | ||
| + | * [=...] is executed by Djeeni reading the actual value of the cell: if [$wsReport!A4] contains the value **24** then the result of this formula is **24** that can be used for calculations or as text value. For date values [=...] reads the underlying number value. | ||
| + | |||
| + | For advanced tips about how to use Excel functions in different process steps see [[en: | ||
| ==== Calculations with data ==== | ==== Calculations with data ==== | ||
| Line 115: | Line 126: | ||
| [+[=wsInput!A5]+[=wsInput!B4]] | [+[=wsInput!A5]+[=wsInput!B4]] | ||
| - | NB: Any type of calculations | + | NB: Any type of calculation |
| + | |||
| + | ==== Dates in formulas ==== | ||
| + | |||
| + | Working with dates in MS Excel is //the most// complex task causing the most headaches. For sure, most of us encountered this situation: a workbook is received with merged worksheets containing a date column. Surprisingly some dates are shown as numbers, while other dates are not recognized by MS Excel and cannot be used for further processing. Also, some dates are simply changing: it was 10 March 2022 at the sender, and it is just displayed as 3 October 2022 at the receiver (March = 3; October = 10). | ||
| + | |||
| + | The (almost) common denominator is the fact that all dates are internally stored in MS Excel as a number. Djeeni uses this number value whenever it is available for reading cell values and calculations. The consequence of it is that the target cells must be pre-formatted to short or long dates otherwise the number values will be seen instead of the date strings. | ||
| + | Dates are also discussed | ||
| ==== Calculations using row number and column letter of a cell ==== | ==== Calculations using row number and column letter of a cell ==== | ||