Weighted Average using Sumproduct with non adject cells.

S

SJHerrell

New Member
Joined
Jan 22, 2019
Messages
1
Here is the Table. I would like to have them in this order.
Measure 10,11,12,18 are in the 2nd set (40% weight) the rest are in the 1st set.
I know if I reorganized them to where the 10, 11, 12, 18 are the last four rows it can be done with these formulas:
1st set: =SUMPRODUCT(F5:F18,E5:E18)/SUM(E5:E18)
2nd set:=SUMPRODUCT(F19:F22,E19:E22)/SUM(E19:E22)
But I do prefer it in the following order:


[TABLE="width: 384"]
<tbody>[TR]
[TD]1st set average
3.44[/TD]
[TD="colspan: 2"]2nd Set average
4.25

[/TD]
[TD]Overall Star Level
[/TD]
[/TR]
[TR]
[TD]60 wgt[/TD]
[TD="colspan: 2"]40 wgt 3.77[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Star Level Measures
[/TD]
[TD]Wgt
[/TD]
[TD]Current Star Level
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 1
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 2
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 3
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 4
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 5
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 6
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 7
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 8
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 9
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 10
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 11
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 12
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 13
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 14
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 15
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 16
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 17
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 18
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
[TABLE="width: 1088"]
<colgroup><col span="17"></colgroup><tbody>[TR]
[TD="colspan: 7"]Here is the Table. I would like to have them in this order. [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 9"]Measure 10,11,12,18 are in the 2nd set (40% weight) the rest are in the 1st set. [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 13"]I know if I reorganized them to where the 10, 11, 12, 18 are the last four rows it can be done with these formulas:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]1st set: =SUMPRODUCT(F5:F18,E5:E18)/SUM(E5:E18)[/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="colspan: 7"]2nd set:=SUMPRODUCT(F19:F22,E19:E22)/SUM(E19:E22)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]But I do prefer it in the following order:[/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][/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1st set average
[/TD]
[TD="colspan: 2"]2nd Set average[/TD]
[TD]Overall Star Level[/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]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3.44[/TD]
[TD="colspan: 2, align: right"]4.25[/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]
[TD][/TD]
[/TR]
[TR]
[TD]60 wgt[/TD]
[TD="colspan: 2"]40 wgt 3.77[/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]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Star Level Measures[/TD]
[TD]Wgt[/TD]
[TD]Current Star Level[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]HELPER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]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]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/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]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]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]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/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]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]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]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]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]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 7[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/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]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]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]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/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]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SECOND[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 11[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SECOND[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SECOND[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/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]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/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]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/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]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 16[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]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]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 17[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]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]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Measure 18[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SECOND[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]the helper column detects if col A cell is 10,11,12,13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]so now use helper column within the sumproduct[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[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][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Updating data in cell, run the macro again and it should not happen.
2 3 4
Replies
34
Views
1K
mumps
mumps
F
  • Solved
How to count the nth set of 6 numbers (repeated 6 numbers based on another column)
Replies
2
Views
182
feni1388
F
C
  • Question Question
Weighting a Trimmed Data Set
2 3
Replies
20
Views
1K
AhoyNC
A
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
218
Pbobas
P
A
  • Question Question
Macro help to average 3 cells in a column, then go to next column and repeat
Replies
1
Views
310
amandabstewart
A

Forum statistics

Threads
1,224,747
Messages
6,180,719
Members
452,995
Latest member
isldboy

Share this page

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