Generate Code with Sum Pattern
Available since AlchemyJ v5.0
Description
The Sum pattern will sum multiple number cells with "+", into a single cell and output the result to the current active cell to reduce the complexity.
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 Sum pattern.
Example 1
For this example, it will to calculate the total performance commission for every staff.
| Name | Department | Rank | Base Bonus | Performance | Performance Commission |
|---|---|---|---|---|---|
| A | Sales | 12000 | [Rank]*0.05 | =IF([@Rank]>10000,[@Rank]0.08,[@Rank]0.03) | Sum([Base Bonus],[Performance]) |
| B | Tech | 9000 | [Rank]*0.05 | =IF([@Rank]>10000,[@Rank]0.08,[@Rank]0.03) | Sum([Base Bonus],[Performance]) |
| C | Sales | 7000 | [Rank]*0.05 | =IF([@Rank]>10000,[@Rank]0.08,[@Rank]0.03) | Sum([Base Bonus],[Performance]) |
The select the cells which need to sumwith Sum pattern, the active cell is Performance Commission, so need select the number cells from H31 to F31, then click Tools->More Tools->Generate Code with Sum Pattern
Then it will combine the calculation cells with "+" into a single and output the result to the active cell (H31)
Then can delete the reference column F and column G. only left the reference column C to E and final result column H.
Example 2
The selected cells are not continuous, can select the cell one by one from left to the right, then the last selected cell in the active cell.
Then it will set the finally calculation result into the active cell (J31)
Note:
- When select cells from left to right the top left cell is active cell
- When select cells from right to left the top right cell is active cell.
- When select cells one by one with press the Ctrl key, the last one cell is active cell.
- Press Enter (move down by default) or Tab (move right by default) to select the active cell.
- When the select is not a number cell the active cell will show #VAULE! error.