CODEWITHSUNDEEP
excelexcel-formula
adityap
adityap

Reputation: 351

SUM of 2-way Weighted Grid in Excel

Example Hi friends, In the above example grid, I am looking for a formula or function that can find the sum of all Data elements with their weighted products. Below is an example pattern in terms of calculation -

Data Range: D5 : N7

Formula I am looking for is : Data (Range) * Weight A (In the same col) * Weight B (In the same row)

D5 * D4 * C5 + E5 * E4 * C5 ... + N5 * N4 * C5 +
D6 * D4 * C6 + .................+ N6 * N4 * C6 +
................................. N7 * N4 * C7

One way I know I could solve this is by calculating SUM at column and row levels and then adding them up to get a 'Grand Total'. However, I have a feeling there is a better and efficient way of solving this.

If anyone has any insights/suggestions, please help!

Upvotes: 1

Views: 77

Answers (4)

nkalvi
nkalvi

Reputation: 2614

DROPping in to take Tom's for fun (using broadcasting):

=LET(
    tb, C4:N7,
    w_a, DROP(TAKE(tb, 1), , 1),
    w_b, DROP(TAKE(tb, , 1), 1),
    data, DROP(tb, 1, 1),
    SUM(w_a * w_b * data)
)

Upvotes: 0

P.b
P.b

Reputation: 11628

=MMULT(D5:N7*D4:N4*C5:C7,SEQUENCE(COUNTA(D4:N4))^0)

Or =BYROW(D5:N7*D4:N4*C5:C7,LAMBDA(x,SUM(x))) Or I believe ETA was rolled out to production (can't test this on my mobile): =BYROW(D5:N7*D4:N4*C5:C7,SUM)

Edit: Oops, I thought you wanted a row wise total. Than a SUM(product) would do, which is already posted.

Upvotes: 0

Excellor
Excellor

Reputation: 875

I did it the hard way:

=LET(_Data,A1:D3,_Rows,ROWS(_Data),_Cols,COLUMNS(_Data),
_HeadRow,TAKE(_Data,1,_Cols*(-1)+1),
_HeadCol,TAKE(_Data,_Rows*(-1)+1,1),
_Body,DROP(_Data,1,1),
_Calc,MAKEARRAY(_Rows-1,_Cols-1,LAMBDA(a,b,INDEX(_Body,a,b)*INDEX(_HeadRow,1,b)*(INDEX(_HeadCol,a,1)))),
_Sum,SUM(_Calc),
_Sum)

enter image description here

Cross referenced with Tom's answer, I'm getting the same.

Advantage of my overcomplicated approach: if your data set changes, you only have one selection of data to make.

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34370

I think it's just

=SUM(D5:N7*D4:N4*C5:C7)

enter image description here

I checked the total using

=D$4*D5*$C5

and copying down and across.

Upvotes: 4

Related Questions