CODEWITHSUNDEEP
excel
jeff m
jeff m

Reputation: 275

Can you convert a scalar into an arbitrary length array in excel

The reason I'm doing this is so I don't have to use matrix multiplication and can use sumproduct instead. I can do it with matrix multiplication, but I'd rather not have the overhead of explaining what that is and how to do it to my colleagues. I need to apply a scalar to two arrays that I'm applying a sumproduct against. The problem is that since the scalar is length 1, it breaks the sumproduct formula. Here is what the formula looks like:

=sumproduct(1/sumif(conditionals), array 2, array 3)

What I would like to do is scale the first array, which is my scalar, based on the size of the other 2 arrays. I have tried this, which I figured wouldn't work, but it should get the idea of what I'm trying to accomplish across:

=sumproduct(rept(1/sumif(conditionals),count(array 2)), array 2, array 3)

The problem here is that the rept function returns a string, which I'm unable to use round() or any other formulas I can think of to make into an 1xN array of that scalar. I should note I'm not trying to cast the variable necessarily, but if there is a way to do that it would likely solve the problem.

Any ideas?

Upvotes: 0

Views: 869

Answers (2)

oscarius
oscarius

Reputation: 236

The following formula should solve your problem:

=SUMPRODUCT(
            ROW(INDIRECT("1:" & COUNT(array 2))) ^ 0 / SUMIF(conditionals),
            array 2,
            array 3
           )

This works as follows:

First obtain the number of elements of array 2:

COUNT(array 2)

Then convert it into a 'virtual' range reference (only rows):

INDIRECT("1:" & COUNT(array 2))

Then determine the row numbers from this virtual range reference, the result of which is {1,2,...,COUNT}:

ROW(INDIRECT("1:" & COUNT(array 2)))

Finally, convert the row number by raising to the power 0:

ROW(INDIRECT("1:" & COUNT(array 2))) ^ 0

This gives you the desired array {1,1,...,1} with COUNT elements, which can be used in the SUMPRODUCT() function.

Upvotes: 1

nutsch
nutsch

Reputation: 5962

Have you tried?

=sumproduct((1/sumif(conditionals))*'array 2'*'array 3')

In addition, wouldn't that be mathematically the same as

=(1/sumif(conditionals))*sumproduct('array 2','array 3')

Upvotes: 2

Related Questions