Generate Code with IFS Pattern

Available since AlchemyJ v5.0

Description

The IFS pattern will combine multiple IF formula cells in the selected range into a single IFS function and output the result to the current active cell to reduce the complexity of nested IFs.

In the selected range it only has one cell is active cell (usually the first one cell), the active cell within a range is not highlighted. The rest of the selected cells are highlighted with a different color. such as below, in the range A1 to C5, A1 is the active cell.

Example

There are a few examples of the IFS pattern.

Example 1

For this example give a users level based on their score.

Customer	VIP	Score	Level
C1	Y	1200	Gold: VIP=Y and Score>=1000 Silver: Score>=500 and Score<1000 Bronze: Score<500

For above scenario use IF formula as below is complexity, easy to make mistakes.

=IF(AND([@VIP]="Y",[@Score]>=1000),"Gold",IF(AND([@Score]>=500,[@Score]<1000),"Silver",IF(AND([@Score]>0,[@Score]<500),"Bronze","")))

Then can separate the above formula to 3 simple formula in 3 cells (F9, G9, H9) then use IFS Pattern to combine them.

Customer	VIP	Score	Level-Gold	Level-Silver	Level-Bronze	Level
C1	Y	1200	=IF(AND([@VIP]="Y",[@Score]>=1000),"Gold","")	=IF(AND([@Score]>=500,[@Score]<1000),"Silver","")	=IF(AND([@Score]>0,[@Score]<500),"Bronze","")

The select the cell range which need to combine with IFS pattern, the active cell is Level so need select the range from I9 to C9, then click Tools->More Tools->Generate Code with IFS Pattern

Then it will combine multiple IF cells in the selected range into a single IFS function and output the result to the active cell (I9)

Then can delete the cell F9 to H9 because their already combine into the active cell I9. only left the reference cell C9 to E9 and active cell.

Customer	VIP	Score	Level
C1	Y	1200	=IFS(AND([@VIP]="Y",[@Score]>=1000),"Gold", AND([@Score]>=500,[@Score]<1000),"Silver", AND([@Score]>0,[@Score]<500),"Bronze", TRUE, "")

Example 2

The IF formula cells not in a range, can select the cell one by one from left to the right (Ctrl+cell) and the last selected cell in the active cell.

Then it will combine multiple IF cells in the selected range into a single IFS function and output the result to the active cell (K9)

Note:

1. When select cells from left to right the top left cell is active cell

2. When select cells from right to left the top right cell is active cell.

3. When select cells one by one with press the Ctrl key, the last one cell is active cell.

4. Press Enter (move down by default) or Tab (move right by default) to select the active cell.

5. When no any IF formula in the selected cells, it will show warning message "No match formula found."