sumproduct with custom ranges

timjohnny

New Member
Joined
Aug 23, 2021
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
Hi all, hope things are going well.

I am trying to calculate a weighted average using sumproduct but have a little problem: I can't just use normal ranges in the sumproduct and sum formulas.
1629798804917.png

Assume row 1 has my values (in yellow) and row 2 has my weights (in green), then normally I would calculate the weighted average as in cell F1. But I have my values and weights in one row, alternating, like in row 4. So when defining my ranges for the sumproduct function and for the sum function, I need to somehow do it such that each range consists only of every other cell in that row, accounting also for another column that always comes in between.

I have hunch that one might put in a condition for each range (or array?) that it consist of the 2nd of every three columns for values, and the 3rd of every three columns for weights, but I'm not sure how to execute that.

Thanks for any help!!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi earthworm, thanks for the response. I haven't used it before but I'll give it a go now!
 
Upvote 0
Book1
ABCDEFGHIJKL
112342.608696
2101112132.608696
3
4110302112531240413
5
Sheet1
Cell Formulas
RangeFormula
G1G1=SUMPRODUCT(A1:D1,A2:D2)/SUM(A2:D2)
G2G2=SUMPRODUCT(B4:K4*C4:L4*(MOD(COLUMN(B4:K4),3)=2))/SUMPRODUCT(C4:L4*(MOD(COLUMN(C4:L4),3)=0))
 
Upvote 0
Book1
ABCDEFGHIJKL
112342.608696
2101112132.608696
3
4110302112531240413
5
Sheet1
Cell Formulas
RangeFormula
G1G1=SUMPRODUCT(A1:D1,A2:D2)/SUM(A2:D2)
G2G2=SUMPRODUCT(B4:K4*C4:L4*(MOD(COLUMN(B4:K4),3)=2))/SUMPRODUCT(C4:L4*(MOD(COLUMN(C4:L4),3)=0))
Hi Phuoc, thanks a lot for the reply. I've tried this formula, but unfortunately it returns #VALUE!. Within the range I'm looking at, I'll have some cells containing "-". Could that be the issue? And if so, do you have any solution in mind?
Thanks again!
 
Upvote 0
If MOD can't handle "-" (the main reason I originally considered sumproduct was that it seems to be able to handle "-"), then I have come to think that maybe there's a way to solve this with a Macro using Step? Assuming I have 2401 columns, then maybe it could be something using
for
j = 2401 To 7 Step -3
and
k = 2400 To 7 Step -3,
Sumproduct(5:j,5:k)/sum(5:j)
?

This probably makes no sense, but I thought there might be something in it maybe?
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top