1

I have a fixed vector, say $A$1:$E$1

I also have a vector per row, say A2:E2, A3:E3, A4:E4...

Now I want output in Column F as

F2 = sumproduct(A1:E1,A2:E2)
F3 = sumproduct(A1:E1,A3:E3)
F4 = sumproduct(A1:E1,A4:E4)
...

Now I want to put these into one array formula, but I don't know how to do it. For example

F2 = Arrayformula(sumproduct($A$1:$E$1,A2:A:E2:E))

Meaning multiply A1-E1 vector with A2:E2, then A3:E3 and so on.

How do I do this?

1 Answer 1

0

=ARRAYFORMULA(IF(LEN(A2:A&B2:B&C2:C&D2:D&E2:E), 
 MMULT(A1:E1*A2:E,TRANSPOSE(COLUMN(A1:E1)^0)), ))

0

8
  • Thank you. I'm having a bit trouble parsing the formula but seems to work.
    – Paul
    May 24, 2019 at 14:59
  • I have played around with this "=A2:A&B2:B&C2:C" and I have no idea what it does. It just gets some of the cells as a string but for a life of me it seems so random what cells it gets. Can you clarify? And then Length of that, ok. But then IF that length? What does that do?
    – Paul
    May 24, 2019 at 15:09
  • COLUMN(A1:E1) just returns a single digit of the column, that to ^0 is then just always "1" isn't it?
    – Paul
    May 24, 2019 at 15:17
  • can you share a copy of your sheet?
    – user0
    May 24, 2019 at 16:24
  • link The cell I wanted to put this into is idx!AB12 The fixed vector is idx!P8:AA8 and the row of vectors is idx!D12:D ... O12:O
    – Paul
    May 24, 2019 at 21:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.