Weighted Average (Less Than - Equal To)

dougztk

New Member
Joined
Jan 10, 2024
Messages
2
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Based on a data set I have I'm looking to calculate a weighted average for each team based on Period (ie. Team A's score up until 08:00 only).

Can this be done using a Sumproduct or is there a different/easier way to do this?
 

Attachments

  • Book1.jpg
    Book1.jpg
    93.1 KB · Views: 10

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
If the Period will always be the same day as in your example:

Book1
ABCDEFG
1TeamPeriodScoreWeightingTeamPeriod
2A11/22/2023 7:00920A11/22/2023 8:00
3A11/22/2023 7:30172540.417
4A11/22/2023 8:001085
5A11/22/2023 8:301225
6c11/22/2023 9:003335
7D11/22/2023 9:304340
8B11/22/2023 7:001255
9A11/22/2023 22:30745
10B11/22/2023 11:002260
11c11/22/2023 7:001440
12c11/22/2023 12:001935
13D11/22/2023 7:00815
Sheet2
Cell Formulas
RangeFormula
F3F3=SUMPRODUCT(--($A$2:$A$13=$F$2),--($B$2:$B$13<=G2),($C$2:$C$13),$D$2:$D$13)/SUMIFS($C$2:$C$13,$A$2:$A$13,$F$2,$B$2:$B$13,"<="&$G$2)
 
Upvote 0
Solution
this is not using the sumproduct.
And, this also only averages the items that are selected in the filter.
Book1
ABCDEFG
1TEAMPERIODSCOREWeightingtime cutoff:08:00:00
2A2023-11-22 07:00920
3A2023-11-22 07:301725
4A2023-11-22 08:001085A11.1923077
5A2023-11-22 08:301225B12
6C2023-11-22 09:003335C14
7D2023-11-22 09:304340D8
8B2023-11-22 07:001255
9A2023-11-22 22:30745
10B2023-11-22 11:002260
11C2023-11-22 07:001440
12C2023-11-22 12:001935
13D2023-11-22 07:00815
Sheet3
Cell Formulas
RangeFormula
G4:G7G4=SUM((F4=$A$2:$A$13)*(ROUND(MOD($B$2:$B$13,1),10)<=$G$1)*($C$2:$C$13)*($D$2:$D$13)) / SUM((F4=$A$2:$A$13)*(ROUND(MOD($B$2:$B$13,1),10)<=$G$1)*($D$2:$D$13))
 
Upvote 0
If the Period will always be the same day as in your example:

Book1
ABCDEFG
1TeamPeriodScoreWeightingTeamPeriod
2A11/22/2023 7:00920A11/22/2023 8:00
3A11/22/2023 7:30172540.417
4A11/22/2023 8:001085
5A11/22/2023 8:301225
6c11/22/2023 9:003335
7D11/22/2023 9:304340
8B11/22/2023 7:001255
9A11/22/2023 22:30745
10B11/22/2023 11:002260
11c11/22/2023 7:001440
12c11/22/2023 12:001935
13D11/22/2023 7:00815
Sheet2
Cell Formulas
RangeFormula
F3F3=SUMPRODUCT(--($A$2:$A$13=$F$2),--($B$2:$B$13<=G2),($C$2:$C$13),$D$2:$D$13)/SUMIFS($C$2:$C$13,$A$2:$A$13,$F$2,$B$2:$B$13,"<="&$G$2)
This works perfectly, thank you!
 
Upvote 0
You're welcome. Thanks for the feedback and welcome to the forum.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

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