1

Excel 365 allows to multiply ranges to get an array as a result.

Example:

# A B C
1 1 0 1
2 0 1 1

Entering in A3 = A1:C1 * A2:C2

will evaluate to {1,0,1} * {0,1,1}

and return an array {0,0,1} spilling in A3:C3

# A B C
3 0 0 1

This operation can also be used in formulas, especially useful in FILTER(), SUMPRODUCT() etc.

Is there a formula in Excel 365 that can take as arguments an arbitrary number of 1-D ranges, multiply them, and return a 1-D array in the same way as above?

For what I found out so far, SUMPRODUCT() and MMULT() can return only a single value, not a 1-D array.

Alternatively, I can write a LAMBDA, but would like to avoid it, if there is a ready-made formula for it.

David Leal
  • 6,373
  • 4
  • 29
  • 56
Yuri Ammosov
  • 15
  • 5
  • 1
    It will be {2,0,0,1}, not {3,0,0,1}. BYCOL could do what you mean. REDUCE could result in the same and can result in 2D arrays. – P.b Nov 25 '22 at 21:33
  • 1
    @p.b I think OP is talking about row numbers in that first column. That said, Reduce would be a great option. Some sample data will be nice. – JvdV Nov 25 '22 at 21:49
  • And MMULT definately can return an array. – P.b Nov 25 '22 at 22:28
  • Correct, First column is Excel row nums for reference. – Yuri Ammosov Nov 25 '22 at 22:49

1 Answers1

0

I am not 100% what do you mean, I would assume you want to multiply all rows of the same column and return a row array with the result per column. You can achieve it in cell E1 using the following formula:

=BYCOL(A1:C3, LAMBDA(col, PRODUCT(col)))

and here is the output:

sample excel file

If you have only positives numbers, then you can use MMULT, based on the following mathematical properties:

log properties

Putting in excel terms using EXP/LN functions in our case it would be:

=EXP(MMULT(TOROW(ROW(A1:C3)/ROW(A1:C3)), LN(A1:C3)))

or using LET to avoid repetitions:

=LET(rng, A1:C3, rows, ROW(rng), u, TOROW(rows/rows), EXP(MMULT(u, LN(rng))))

You get the same result.

Note: rows/rows just returns the unit vector with the same number of rows as A1:C3.

David Leal
  • 6,373
  • 4
  • 29
  • 56
  • 1
    I wanted to reproduce range multiplication, using *a single built-in formula*. The first example is perhaps the closest to it, except it assumes a continuous range of vectors - which can be done via HSTACK() or VTACK(). Anyway, thank you for laying it all out, I think it closes the question. – Yuri Ammosov Nov 27 '22 at 00:26