Business Modeling Best Practice
Avoid using VLOOKUP. Use INDEX-MATCH instead.
When selecting a range for lookup, avoid using the entire row/column as reference (A:C). Use A1:C100 instead.
When using VLOOKUP or MATCH, sort the lookup array if possible since this speeds up the lookup.
When using VLOOKUP or MATCH, fill in Match Type.
Avoid using INDIRECT and OFFSET since AlchemyJ generated code cannot run in High Performance mode when they are used. They are also hard to debug, volatile, slow, single thread, do not support row/column added/deleted/moved.
Try to design your worksheet so that cells can be fit into a rectangular window with minimal empty cells. This reduces the memory used during runtime.
Use ajAddress for filling in address values in the definition template instead of hard-coding a string value. Using ajAddress supports row added/changed/deleted since it is a function.
Use ajAddress when a cell/range address string is required as the input parameter of a function. This builds dependency on the reference cell.
Do not use array formulas except for using them for AlchemyJ Extended Functions. Array formulas are slow and AlchemyJ does not fully support them.
All formatting such as Date, the decimal place for numbers will be lost in compiled code. Do not rely on them for data formatting. Use TEXT instead.
Convert dates into strings before feeding them into JSON string creation functions (ajJSON, ajJSONFromTable, ajJSONFromSchema) since JSON has no date data type. Alternatively, define the date format in a data dictionary and add the data dictionary into the data relationship schema used by ajJSONFromSchema.
Use Manual Calculation mode in Excel.
Use IFERROR instead of IF and ISERROR combo.
You can see the parameter of an AlchemyJ Extended Function by typing in the function in a formula and press CTRL+SHIFT+A.
Do not use formula while a static value can be used instead.
Remove unused names. Do not remove names that start with AlchemyJ_ as they are names used by AlchemyJ Studio.
Avoid circular reference.
Prioritize multiple-condition SUMIFS, COUNTIFS, and other IFS family functions. Put the most restrictive condition first so that subsequent conditions only need to look at the smallest number of rows.
When using nested IF, evaluate conditions that happen the most frequently first so other nested conditions do not need to be evaluated most of the time.