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 ajGetAddress for filling in address values in the definition template instead of hard-coding a string value. Using ajGetAddress supports row added/changed/deleted since it is a function.
Use ajGetAddress when a cell/range address string is required as the input parameter of a function. This builds dependency on the reference cell.
When a method input parameter is a large object or a large list of objects, avoid defining Json-path for each property. Instead, get the whole JSON from the input and use ajPopulateJsonToTable to convert the JSON string into tables. It should be faster since Json-path is a query and it is slow.
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 (ajMakeJson, ajMakeJsonFromTable, ajMakeJsonFromSchema) since JSON has no date data type.
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.