Array SumProduct Average?

[ClimoC]

Hi again all

Not sure where to start with this one, so I'll try to keep it brief.

We have two sets of cells on separate rows that contain lookups to a separate table for each row. These return some decimal values.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Name1[/TD]
[TD]Name2[/TD]
[TD]Name3[/TD]
[TD]Name4[/TD]
[TD]Name5[/TD]
[/TR]
[TR]
[TD]Table1 Lookup[/TD]
[TD]1.02[/TD]
[TD]1.12[/TD]
[TD]1.3[/TD]
[TD]0.89[/TD]
[TD]0.91[/TD]
[/TR]
[TR]
[TD]Table2 Lookup[/TD]
[TD]1.07[/TD]
[TD]1.14[/TD]
[TD]1.2[/TD]
[TD]0.99[/TD]
[TD]0.95[/TD]
[/TR]
</tbody>[/TABLE]

These are then multiplied by a percentage, using the same 'name' for a lookup; these are hard-written into the cells

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Name1[/TD]
[TD]Name2[/TD]
[TD]Name3[/TD]
[TD]Name4[/TD]
[TD]Name5[/TD]
[/TR]
[TR]
[TD](Total 100%)[/TD]
[TD]27%[/TD]
[TD]34%[/TD]
[TD]3%[/TD]
[TD]4%[/TD]
[TD]32%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Giving us 2 Product Rows. We then sum each row and pick the one that is the largest sum

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name1[/TD]
[TD]Name2[/TD]
[TD]Name3[/TD]
[TD]Name4[/TD]
[TD]Name5[/TD]
[TD]Sum of Row[/TD]
[/TR]
[TR]
[TD]0.2754[/TD]
[TD]0.3808[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]1.19[/TD]
[/TR]
[TR]
[TD]0.2889[/TD]
[TD]0.3876[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]1.17[/TD]
[/TR]
</tbody>[/TABLE]


Including the lookups by the name, is there a way to put this in a single array formula (even if it's wrapped in an If statement to pick the biggest one?)

Thanks
C

 

[Marcelo Branco]

Not sure i understand what you want. Maybe this...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD]

Name1​

[/TD]
[TD]

Name2​

[/TD]
[TD]

Name3​

[/TD]
[TD]

Name4​

[/TD]
[TD]

Name5​

[/TD]
[TD][/TD]
[TD]

Name1​

[/TD]
[TD]

Name2​

[/TD]
[TD]

Name3​

[/TD]
[TD]

Name4​

[/TD]
[TD]

Name5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Table1 Lookup​

[/TD]
[TD]

1,02​

[/TD]
[TD]

1,12​

[/TD]
[TD]

1,3​

[/TD]
[TD]

0,89​

[/TD]
[TD]

0,91​

[/TD]
[TD][/TD]
[TD]

27%​

[/TD]
[TD]

34%​

[/TD]
[TD]

3%​

[/TD]
[TD]

4%​

[/TD]
[TD]

32%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Table2 Lookup​

[/TD]
[TD]

1,07​

[/TD]
[TD]

1,14​

[/TD]
[TD]

1,2​

[/TD]
[TD]

0,99​

[/TD]
[TD]

0,95​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Result (Max)​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD="bgcolor: #D9D9D9"]

1,0561​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in A6
=MAX(MMULT(B2:F3*H2:L2,{1;1;1;1;1}))

Remark: I got different values for the sums of rows: 1.022 and 1.0561 (not 1.19 and 1.17 as in your post). So the largest sum is 1.0561.

Hope this helps

M.

 

[ClimoC]

Thanks Marcelo

The figures I put in (1.19 and 1.17) were just examples (as I couldn't be bothered transcribing all the real data)

What you've kindly provided me with looks like it would remove the need for the extra 2 summary rows whcih are currently summed and ranked.

Is there a formula I'm forgetting about which could return the array values, B2:F2 and B3:F3 in your example? I was hoping some sort of sumproduct hybrid could spare all the work of generating the table rows as above.

Is it clearer if I word it like this?

Two arrays from two tables, using the same key (name) as a lookup, are multiplied against a third array. The totals of all the products in the two array*array are summed to see which one is bigger, and return that bigger value to a cell

 

[Marcelo Branco]

Is there a formula I'm forgetting about which could return the array values, B2:F2 and B3:F3 in your example? I was hoping some sort of sumproduct hybrid could spare all the work of generating the table rows as above.

Is it clearer if I word it like this?

We need more information.
How the arrays B2:F2 and B2:F3 are being generated? Could you provide the formulas and data sample?

M.

 

[ClimoC]

Sorry. I don't know what's wrong with me. I'm a VB developer... I should really just write a function to do this all and return a value... I think I was just so intent on sticking to formulas for this one...

 

[Marcelo Branco]

Sorry. I don't know what's wrong with me. I'm a VB developer... I should really just write a function to do this all and return a value... I think I was just so intent on sticking to formulas for this one...

Probably a UDF (User Defined Function) can easily do the job. I was just curious if it was possible a solution using formulas ...

M.

 

[ClimoC]

I think I'll just write the VBA function to do this, but if you're interested in solving it (possibly for others) then it's like this:

Array 1
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name1[/TD]
[TD]Name2[/TD]
[TD]Name3[/TD]
[TD]Name4[/TD]
[TD]Name5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]=vlookup(A1,Sheet2!B43:P778,3,0)[/TD]
[TD]=vlookup(B1,Sheet2!B43:P778,3,0)[/TD]
[TD]=vlookup(C1,Sheet2!B43:P778,3,0)[/TD]
[TD]=vlookup(D1,Sheet2!B43:P778,3,0)[/TD]
[TD]=vlookup(E1,Sheet2!B43:P778,3,0)[/TD]
[/TR]
</tbody>[/TABLE]

Array 2

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Name1[/TD]
[TD]Name2[/TD]
[TD]Name3[/TD]
[TD]Name4[/TD]
[TD]Name5[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]=vlookup(A6,Sheet3!B43:P778,3,0)[/TD]
[TD]=vlookup(B6,Sheet3!B43:P778,3,0)[/TD]
[TD]=vlookup(C6,Sheet3!B43:P778,3,0)[/TD]
[TD]=vlookup(D6,Sheet3!B43:P778,3,0)[/TD]
[TD]=vlookup(E6,Sheet3!B43:P778,3,0)

[/TD]
[/TR]
</tbody>[/TABLE]

Array 3
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Name1[/TD]
[TD]Name2[/TD]
[TD]Name3[/TD]
[TD]Name4[/TD]
[TD]Name5[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]=vlookup(A1,Sheet4!B3:O15,10,0)[/TD]
[TD]=vlookup(B1,Sheet4!B3:O15,10,0)[/TD]
[TD]=vlookup(C1,Sheet4!B3:O15,10,0)[/TD]
[TD]=vlookup(D1,Sheet4!B3:O15,10,0)[/TD]
[TD]=vlookup(E1,Sheet4!B3:O15,10,0)

[/TD]
[/TR]
</tbody>[/TABLE]


Then we go:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]=A2*A12[/TD]
[TD]=B2*B12[/TD]
[TD]=C2*C12[/TD]
[TD]=D2*D12[/TD]
[TD]=E2*E12[/TD]
[TD]=Sum(A20:E20)[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]=A7*A12[/TD]
[TD]=B7*B12[/TD]
[TD]=C7*C12[/TD]
[TD]=D7*D12[/TD]
[TD]=E7*E12[/TD]
[TD]=Sum(A21:E21)[/TD]
[/TR]
</tbody>[/TABLE]


So that we can finally go:

=MAX(F20,F21)

That's the value that we need to get to, the Max one.

 

[Marcelo Branco]

I think I'll just write the VBA function to do this, but if you're interested in solving it (possibly for others)

Maybe something like this
An example using your data sample above but with the 3 tables in the same sheet


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Names​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

Table1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #C5D9F1"]

Table2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #FDE9D9"]

Table3​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Name1​

[/TD]
[TD][/TD]
[TD]

Name3​

[/TD]
[TD]

1,3​

[/TD]
[TD][/TD]
[TD]

Name2​

[/TD]
[TD]

1,14​

[/TD]
[TD][/TD]
[TD]

Name4​

[/TD]
[TD]

4%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Name2​

[/TD]
[TD][/TD]
[TD]

Name2​

[/TD]
[TD]

1,12​

[/TD]
[TD][/TD]
[TD]

Name1​

[/TD]
[TD]

1,07​

[/TD]
[TD][/TD]
[TD]

Name6​

[/TD]
[TD]

0%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Name3​

[/TD]
[TD][/TD]
[TD]

Name6​

[/TD]
[TD]

0,88​

[/TD]
[TD][/TD]
[TD]

Name6​

[/TD]
[TD]

1,2​

[/TD]
[TD][/TD]
[TD]

Name1​

[/TD]
[TD]

27%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Name4​

[/TD]
[TD][/TD]
[TD]

Name5​

[/TD]
[TD]

0,91​

[/TD]
[TD][/TD]
[TD]

Name4​

[/TD]
[TD]

0,99​

[/TD]
[TD][/TD]
[TD]

Name3​

[/TD]
[TD]

3%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Name5​

[/TD]
[TD][/TD]
[TD]

Name4​

[/TD]
[TD]

0,89​

[/TD]
[TD][/TD]
[TD]

Name3​

[/TD]
[TD]

1,2​

[/TD]
[TD][/TD]
[TD]

Name2​

[/TD]
[TD]

34%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Name1​

[/TD]
[TD]

1,02​

[/TD]
[TD][/TD]
[TD]

Name5​

[/TD]
[TD]

0,95​

[/TD]
[TD][/TD]
[TD]

Name5​

[/TD]
[TD]

32%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

Max Formula​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

1,0561​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in A10
=MAX(SUMPRODUCT(SUMIF(C2:C7,A2:A6,D2:D7)*SUMIF(I2:I7,A2:A6,J2:J7)),SUMPRODUCT(SUMIF(F2:F7,A2:A6,G2:G7)*SUMIF(I2:I7,A2:A6,J2:J7)))

M.