How to use SUMPRODUCT with binary values as string in cell?

Question

I want to use SUMPRODUCT so that it will work inside cell that contains string of binary numbers for example:

Cell A1 = "011001" Cell B1 = "123456"

Now I want to use SUMPRODUCT to perform weighted average, cell A1 contain numbers and cell B1 contain weights, proper formula should work like this:

1st element (0) = weight (1)

2nd element (1) = weight (2)

3rd element (1) = weight (3)

4th element (0) = weight (4)

5th element (0) = weight (5)

6th element (1) = weight (6)

This imaginary formula SUMPRODUCT(A1;B1)/SUM(B1) should be equal =~52% If someone know any solution for that problem thank you very much in advance.

Answer 1

This will do what you want:

 =SUMPRODUCT(MID(A1,ROW(1:6),1)*MID(B1,ROW(1:6),1))/SUMPRODUCT(--MID(B1,ROW(1:6),1))

If you want to make it more dynamic, if your numbers are not always 6 digits:

=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:" & MIN(LEN(A1),LEN(B1)))),1)*MID(B1,ROW(INDIRECT("1:" & MIN(LEN(A1),LEN(B1)))),1))/SUMPRODUCT(--MID(B1,ROW(INDIRECT("1:" & MIN(LEN(A1),LEN(B1)))),1))

This will now take the shortest of the two numbers and use that as the number length. So if A1 only has 5 digits and B1 has 6 it will only use the first five.

If B1 is five and A1 is six it will only use the first five of both.

As the number length grows or shrinks the formula will adapt.

Answer 2

Using the curly brace syntax for arrays, the example looks like this after coercion: =SUMPRODUCT({0,1,1,0,0,1},{1,2,3,4,5.6})

Answer 3

If the weights are always 1,2,3,4,5,6 in that order then use

=SUMPRODUCT(VALUE(MID(A1,{1,2,3,4,5,6},1)),{1,2,3,4,5,6})/21

If the 6 weights can be different from this then use

=SUMPRODUCT(VALUE(MID(A1,{1,2,3,4,5,6},1)),VALUE(MID(B1,{1,2,3,4,5,6},1)))/SUM(VALUE(MID(B1,{1,2,3,4,5,6},1)))

If there are greater or fewer than 6 weights adjust the array {1,2,3,4,5,6} accordingly.

If binary string in A1 is shorter in length than the array size you will get a #VALUE! error in both cases. If the weights string in B1 is shorter than the array size you will get the same error in the second case (B1 is not used in the first case). If either string has more characters than the size of the array only the leftmost characters up to the size of the array will be used.