CODEWITHSUNDEEP
arraysexcel
Helen
Helen

Reputation: 617

Excel: dynamic array formula

enter image description here

I have an Excel sheet as in the picture. I want to make a variable array formula that uses the value in cell E1.

Cell D4 has the following formula, which works fine:

{=SUM(A3:A5*B3:B5)}

But, cell D5, where I try to use E5 together with OFFSET does not work:

{=SUM(A3:OFFSET(A3,E1,0)*B3:B5)}

Could anybody explain how to make this "dynamic" array formula work?

Upvotes: 1

Views: 219

Answers (2)

user4039065
user4039065

Reputation:

Try to reshape the range with OFFSET rather than changing the starting position.

=SUM(OFFSET(A3, 0, 0, E1, 1)*OFFSET(b3, 0, 0, E1, 1))

This is the equivalent of,

=sum(A3:A5*B3:B5)

Upvotes: 1

XOR LX
XOR LX

Reputation: 7762

In your example, the portion

A3:OFFSET(A3,E1,0)

would resolve to the range

A3:A6

i.e. a range comprising 4 rows. Evidently multiplying this range with one comprising just 3 rows (B3:B5) will error.

In any case, it's preferable to avoid the volatile OFFSET.

Try:

=SUM(A3:INDEX(A3:A5,E1)*B3:B5)

with CSE (although I have to confess that I'm not quite sure why you aren't using SUMPRODUCT without CSE).

I'm also not sure I understand the extent to which this set-up is 'dynamic', since the value in E1 must be consistent with the number of rows hard-coded within the other range in the formula (B3:B5 here). As such, in reality you only have one choice for the value in E1; all others will produce an error. Unless you intend to incorporate that value within the second range also? For example:

=SUM(A3:INDEX(A3:A5,E1)*B3:INDEX(B3:B5,E1))

Regards

Upvotes: 2

Related Questions