Task: To output an array of scalar products based on input array of boolean values and scalars.
Requirements: The solution needs to be formulaic (i.e. contained in one cell) and done without the use of VBA -> the solution needs to be dynamic to accommodate different input arrays.
Input array A (Boolean values)
| >= | 2023 | 2024 | 2025 | 2026 |
|---|---|---|---|---|
| 2023 | 1 | 0 | 0 | 0 |
| 2024 | 1 | 1 | 0 | 0 |
| 2025 | 1 | 1 | 1 | 0 |
| 2026 | 1 | 1 | 1 | 1 |
Input array B (Scalar values)
| 2023 | 2024 | 2025 | 2026 |
|---|---|---|---|
| 1.25 | 1 | 1.2 | 1.05 |
| 1.35 | 1.1 | 1 | 1.2 |
| 1.25 | 1.15 | 1.05 | 1.05 |
| 1.3 | 1 | 1.1 | 1.15 |
| 1.25 | 1.1 | 1.4 | 1.35 |
Output array (Compounded scalars)
| 2023 | 2024 | 2025 | 2026 |
|---|---|---|---|
| 1.25 | 1.25 | 1.5 | 1.575 |
| 1.35 | 1.485 | 1.485 | 1.782 |
| 1.25 | 1.4375 | 1.509375 | 1.58484375 |
| 1.3 | 1.3 | 1.43 | 1.6445 |
| 1.25 | 1.375 | 1.925 | 2.59875 |
In practice: the columns of Output array are comprised of row-wise products of the Input array B. For example the first column is only the 2023 scalars, but the third is 2023 * 2024 * 2025 on each row (i.e. 1.25 * 1 * 1.2, for the first value of the third column). As such, the Output array has the same amout of columns as Input array B, with each column's values being a product of the current and the preceding columns of Input array B.
Note: The format of Input array A is irrelevant in the sense that the boolean values just need to indicate which elements of a particular row are multiplied together -> the array can be changed if needed, but Input array B should remain as provided.
Bruteforce solution: This task can be completed with MAKEARRAY() but it becomes exceedingly inefficient when Input array B has thousands of rows.
Solution found:
No comments:
Post a Comment