CODEWITHSUNDEEP
arraysexcelexcel-formulaaggregate-functions
ahi324
ahi324

Reputation: 439

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

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

Interestingly:

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.

Solutions (Thanks to Contributors Below)

Upvotes: 1

Views: 198

Answers (2)

P.b
P.b

Reputation: 11578

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))

enter image description here

Upvotes: 1

Jos Woolley
Jos Woolley

Reputation: 9062

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))

Upvotes: 2

Related Questions