Excel: Problems w. INDIRECT, Arrays, and Aggregate Functions (SUM, MAX, etc.)-CodePudding

Objective

I have a Microsoft Excel spreadsheet containing a price list that may change over time (B2:B5 in the example). Separately, I have a budget that too may change over time (D2). I am attempting to construct a formula for E2 to output the number of items that can be purchased with the budget in D2. Thereafter, I'll attempt to construct formulas to output any change that would be made (F2) and a comma-delimited list of purchasable items (G2).

Note: It unfortunately isn't possible to add an intermediate calculation column to the list, such as a running total. As such, I'm trying for formulas for single cells (i.e., E2, F2, and G2).

Note: I'm using Excel for Mac 2019.

       A         B       C       D         E         F                  G
   --------- --------- ----- --------- ------- --------- --------------------------- 
1 |  Label  |  Price  |     | Budget  | Items | Change  |          Item(s)          |
   --------- --------- ----- --------- ------- --------- --------------------------- 
2 | Item #1 | $ 10.00 |     | $ 40.00 |     3 | $  4.50 | Item #1, Item #2, Item #3 |
   --------- --------- ----- --------- ------- --------- --------------------------- 
3 | Item #2 | $ 20.00 |     |         |       |         |                           |
   --------- --------- ----- --------- ------- --------- --------------------------- 
4 | Item #3 | $  5.50 |     |         |       |         |                           |
   --------- --------- ----- --------- ------- --------- --------------------------- 
5 | Item #4 | $ 25.00 |     |         |       |         |                           |
   --------- --------- ----- --------- ------- --------- --------------------------- 
6 | Item #5 | $ 12.50 |     |         |       |         |                           |
   --------- --------- ----- --------- ------- --------- ---------------------------

For E2, I've attempted:

{=MAX(N(SUM(INDIRECT("$B$2:$B$"&ROW($B$2:$B$6)))<=$D2)*ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6)) 1)}

Though, the above values and this formula result in an output of -1.

Note: The formula for F2 and G2 seemingly easily follow E2; e.g. {=$D2-SUM(IF((ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6)) 1)<=$E2,$B$2:$B$6,0))} and {=TEXTJOIN(", ",TRUE,INDIRECT("$A$2:$A$"&(MIN(ROW($B$2:$B$6)) $E2-1)))} seem to work well, respectively.

Observations

{="$B$2:$B$"&ROW($B$2:$B$6)} evaluates to {"$B$2:$B$2";"$B$2:$B$3";...;"$B$2:$B$6"} (as desired);
{=INDIRECT("$B$2:$B$"&ROW($B$2:$B$6)) should evaluate to the equivalent of {{$B$2:$B$2},{$B$2:$B$3},...,{$B$2:$B$6}}; though, as a 1x5 multi-cell array formula, evaluates to the equivalent of {#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!} and, with F9 does to {10;#N/A;#N/A;#N/A;12.5};
{=SUM(INDIRECT("$B$2:$B$"&ROW($B$2:$B$6)))<=$D2}, as a 1x5 multi-cell array formula, evaluates to the equivalent of {TRUE;TRUE;TRUE;FALSE;FALSE} (as desired); though, with F9 does to #VALUE!;
{=N(SUM(INDIRECT("$B$2:$B$"&ROW($B$2:$B$6)))<=$D2)}, as a 1x5 multi-cell array formula, evaluates to the equivalent of 1;1;1;0;0 (as desired); though, with F9 does again to #VALUE!;
{=N(SUM(INDIRECT("$B$2:$B$"&ROW($B$2:$B$6)))<=$D2)*ROW($B$2:$B$6), as as 1x5 multi-cell array formula, evaluates to the equivalent of {2,3,4,0,0} (as desired); though, with F9 does to {#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!};
{=N(SUM(INDIRECT("$B$2:$B$"&ROW($B$2:$B$6)))<=$D2)*ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6)) 1}, as a 1x5 multi-cell array formula, evaluates to the equivalent of {1,2,3,-1,-1} (as desired); though, with F9 does again to {#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}; and,
{=MAX(N(SUM(INDIRECT("$B$2:$B$"&ROW($B$2:$B$6)))<=$D2)*ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6)) 1)} evaluates to -1

Interestingly:

If {=N(SUM(INDIRECT("$B$2:$B$"&ROW($B$2:$B$6)))<=$D2)*ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6)) 1} is placed as the multi-cell array formula in, say, E10:E14, a =MAX($E$10:$E$14) results in 3 (as desired).

Speculation

At present, I'm speculating that, when entered as a single cell array formula, the INDIRECT is not being assessed to be array producing and/or the SUM, as part of a single cell array formula, is not producing an array result.

Please assist. And, thank you in advance.

CodePudding user response：

The first point to make, as I mentioned in the comments, is that it must be understood that piecemeal evaluation of a formula - via highlighting subsections of that formula and committing with F9 within the formula bar - will not necessarily correspond to the actual evaluation.

Evaluation via F9 in the formula bar always forces that part to be evaluated as an array. Though this is misleading, since the overall construction may not actually evaluate that part as an array.

The second point to make is that SUM cannot iterate over an array of ranges, though SUBTOTAL, for example, can, so replacing SUM with SUBTOTAL (9, in your current formula should work.

However, you would still be left with a construction which is volatile, so I would recommend this non-volatile alternative:

=MATCH(1,0/(MMULT(N(ROW(B2:B6)>=TRANSPOSE(ROW(B2:B6))),B2:B6)<=D2))

CodePudding user response：

In E2 you can use: =MATCH(TRUE,--SUBTOTAL(9,OFFSET(B2:B6,,,ROW(B2:B6)))>=D2,0)

In F2 you can use: =D2-SUM(B2:INDEX(B2:B6,E2))

In G2 you can use: =TEXTJOIN(", ",1,A2:INDEX(A2:A6,E2))