Weighting a Trimmed Data Set

[cmfuld]

Trying to do a forecast. I want to take the most recent 8 weeks and trim out the highest and lowest value. This would give me 6 weeks of data. Most recent two weeks would be weighted at 25%, middle two weeks would be weighted at 15% and oldest two weeks would be at 10%.

We tried to do the forecast with the Trim Mean formula, which would drop the high and low value, but this gives equal weighted to each of the six weeks.

We believe that weighting the stores will show us changes in the business sooner.

In the example below, I got the weighted average, but I had to manually take out the mIn and Max from my data set. I need to figure out a way to do this with a formula or a set of formulas.

​

2023​

2023​

2023​

2023​

2023​

2023​

2023​

2023​

10%​

10%​

15%​

15%​

25%​

25%​

Wk5

Wk6

Wk7

Wk8

Wk9

Wk10

Wk11

Wk12

Trim Mean

Min

Max

Wgt Avg

36316​

55%​

13%​

19%​

33%​

22%​

22%​

45%​

22%​

27.1%​

13%​

55%​

19%​

33%​

22%​

22%​

45%​

22%​

28.5%​

36536​

13%​

17%​

27%​

29%​

-3%​

19%​

18%​

10%​

17.2%​

-3%​

29%​

13%​

17%​

27%​

19%​

18%​

10%​

16.8%​

36571​

5%​

-2%​

-4%​

-5%​

6%​

-1%​

-3%​

-4%​

-1.5%​

-5%​

6%​

5%​

-2%​

-4%​

-1%​

-3%​

-4%​

-2.2%​

 

[awoohaw]

well, please use the xl2bb add in to share an actual copy of your worksheet section that has the formulas you are using. Pasting into a table is okay as it gives us values to work with. But, when you already have two solutions that are in one cell and seem to work, seeing how you build the formulas is critical.

In your weighted average calculation how are you selecting the 6 values to use in columns with % headers?
And what is the purpose of the percentages in the bottom 3 rows when you have no data to left?

 

[awoohaw]

i'm really confused with your numbers on the right?

the top row is:

19%

33%

22%

22%

45%

22%

ranking this would be:

1

5

2

3

6

4

And you seem to be multiplying them by:

10%

10%

15%

15%

25%

25%

and it seem you are multiplying by the incorrect percentages if you are multiplying by the order of the cells above:

10% * 19%

10% * 33%

15% * 22%

15% * 22%

25% * 45%

25% * 22%

when it should be:

10% *19%

10% * 22%

15% * 22%

15% * 22%

25% * 33%

25% * 45%

but I'm also curious as the percents of the 8 records don't even seem to match.

 

[cmfuld]

Sorry, figured out how to do the XBB add-in. My excel wasn't letting me do this earlier.

I manually did the six values in Y:AD and then used sumproduct to calculate the weighted average. I want the formula to automagically grab the six values going forward and then weight the numbers for me.

I removed the percentages in the bottom rows. They were just to show the weighting.

The conditional formatting and the Min/Max columns are just for information purposes.

Copy of Sales Index_WK12 - 2023 cf.xlsb
	G			J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
3																			Weighting
4				2023	2023	2023	2023	2023	2023	2023	2023								10%	10%	15%	15%	25%	25%
5				Wk5	Wk6	Wk7	Wk8	Wk9	Wk10	Wk11	Wk12			Trim Mean		Min	Max									Weighted Average		Sum(Drop(Drop
6	36316			55%	13%	19%	33%	22%	22%	45%	22%			27.1%		13%	55%		18.6%	33.3%	21.6%	22.2%	45.1%	21.8%		28.5%		30.2%
7	36536			13%	17%	27%	29%	-3%	19%	18%	10%			17.2%		-3%	29%		13%	17%	27%	19%	18%	10%		16.8%		18.9%
8	36571			5%	-2%	-4%	-5%	6%	-1%	-3%	-4%			-1.5%		-5%	6%		5%	-2%	-4%	-1%	-3%	-4%		-2.2%		-0.5%
9
Sheet1

Cell Formulas
Range	Formula
V6:V8	V6	=MIN(J6:Q6)
W6:W8	W6	=MAX(J6:Q6)
Y6:AD6,AB7:AD8	Y6	=L6
Y7:AA8	Y7	=J7
T6:T8	T6	=TRIMMEAN(J6:Q6,0.25)
AF6:AF8	AF6	=SUMPRODUCT(Y6:AD6*Y$4:AD$4)
AH6:AH8	AH6	=SUM(DROP(DROP(SORT(J6:Q6,1,1,TRUE),,-1),,1)*TEXTSPLIT(TEXTJOIN(",",,0.1,0.1,0.15,0.15,0.25,0.25),","))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J6:Q8,J14:Q29	Expression	=J6=$W6	text	NO
J6:Q8,J14:Q29	Expression	=J6=$V6	text	NO

 

[cmfuld]

i'm really confused with your numbers on the right?

the top row is:

19%

33%

22%

22%

45%

22%

ranking this would be:

1

5

2

3

6

4

And you seem to be multiplying them by:

10%

10%

15%

15%

25%

25%

and it seem you are multiplying by the incorrect percentages if you are multiplying by the order of the cells above:

10% * 19%

10% * 33%

15% * 22%

15% * 22%

25% * 45%

25% * 22%

when it should be:

10% *19%

10% * 22%

15% * 22%

15% * 22%

25% * 33%

25% * 45%

but I'm also curious as the percents of the 8 records don't even seem to match.

In my example. Column AD is the most recent week that isn't a min or Max. Column AC is the second most recent week (non-min/max), Column AB is the 3rd most recent week...

In the first row, This corresponds to weeks 7-12. In the second row, this is weeks 5 -7 and then weeks 10-12

 

[awoohaw]

great that you got that working. Thanks!
But, your sumproduct is not multiplying by the correct weighted averages. See you have the non min non max values in date order, but your weighted percentages are not in the same order. Therefore, I think you are multiplying some values by the wrong weights.

 

[awoohaw]

and i think you should forget MIN/MAX comparisons. Just use the SMALL function. and get the 2nd-7th smallest values in the 8 week range. They are already in an array. Then multiply that in that correctly ordered array from smallest to larges by the correct order of your percentage weights. Its all in one cell.

 

[AhoyNC]

Maybe another option:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2		2023	2023	2023	2023	2023	2023	2023	2023			10%	10%	15%	15%	25%	25%				One
3		Wk5	Wk6	Wk7	Wk8	Wk9	Wk10	Wk11	Wk12										Wgt Avg		Formula
4	36316	55%	13%	19%	33%	22%	22%	45%	22%			19%	33%	22%	22%	45%	22%		28.55%		28.55%
5	36536	13%	17%	27%	29%	-3%	19%	18%	10%			13%	17%	27%	19%	18%	10%		16.90%		16.90%
6	36571	5%	-2%	-4%	-5%	6%	-1%	-3%	-4%			5%	-2%	-4%	-1%	-3%	-4%		-2.20%		-2.20%
Sheet1

Cell Formulas
Range	Formula
L4:Q6	L4	=FILTER(B4:I4,(B4:I4>MIN(B4:I4))*(B4:I4<MAX(B4:I4)))
S4:S6	S4	=SUMPRODUCT($L$2:$Q$2,L4#)/SUM($L$2:$Q$2)
U4:U6	U4	=SUMPRODUCT((FILTER(B4:I4,(B4:I4>MIN(B4:I4))*(B4:I4<MAX(B4:I4))))*($L$2:$Q$2))/SUM($L$2:$Q$2)
Dynamic array formulas.

 

[awoohaw]

Maybe another option:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2		2023	2023	2023	2023	2023	2023	2023	2023			10%	10%	15%	15%	25%	25%				One
3		Wk5	Wk6	Wk7	Wk8	Wk9	Wk10	Wk11	Wk12										Wgt Avg		Formula
4	36316	55%	13%	19%	33%	22%	22%	45%	22%			19%	33%	22%	22%	45%	22%		28.55%		28.55%
5	36536	13%	17%	27%	29%	-3%	19%	18%	10%			13%	17%	27%	19%	18%	10%		16.90%		16.90%
6	36571	5%	-2%	-4%	-5%	6%	-1%	-3%	-4%			5%	-2%	-4%	-1%	-3%	-4%		-2.20%		-2.20%
Sheet1

Cell Formulas
Range	Formula
L4:Q6	L4	=FILTER(B4:I4,(B4:I4>MIN(B4:I4))*(B4:I4<MAX(B4:I4)))
S4:S6	S4	=SUMPRODUCT($L$2:$Q$2,L4#)/SUM($L$2:$Q$2)
U4:U6	U4	=SUMPRODUCT((FILTER(B4:I4,(B4:I4>MIN(B4:I4))*(B4:I4<MAX(B4:I4))))*($L$2:$Q$2))/SUM($L$2:$Q$2)
Dynamic array formulas.

@AhoyNC , I was under the impression there needed to be a sort from smallest to largest or, and then multiply by the weight percentages.
But I could be wrong I thought of filter, but multiplying the first two arguments in the filter get the desired array, i think.

 

[cmfuld]

@AhoyNC , I was under the impression there needed to be a sort from smallest to largest or, and then multiply by the weight percentages.
But I could be wrong I thought of filter, but multiplying the first two arguments in the filter get the desired array, i think.

Maybe another option:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2		2023	2023	2023	2023	2023	2023	2023	2023			10%	10%	15%	15%	25%	25%				One
3		Wk5	Wk6	Wk7	Wk8	Wk9	Wk10	Wk11	Wk12										Wgt Avg		Formula
4	36316	55%	13%	19%	33%	22%	22%	45%	22%			19%	33%	22%	22%	45%	22%		28.55%		28.55%
5	36536	13%	17%	27%	29%	-3%	19%	18%	10%			13%	17%	27%	19%	18%	10%		16.90%		16.90%
6	36571	5%	-2%	-4%	-5%	6%	-1%	-3%	-4%			5%	-2%	-4%	-1%	-3%	-4%		-2.20%		-2.20%
Sheet1

Cell Formulas
Range	Formula
L4:Q6	L4	=FILTER(B4:I4,(B4:I4>MIN(B4:I4))*(B4:I4<MAX(B4:I4)))
S4:S6	S4	=SUMPRODUCT($L$2:$Q$2,L4#)/SUM($L$2:$Q$2)
U4:U6	U4	=SUMPRODUCT((FILTER(B4:I4,(B4:I4>MIN(B4:I4))*(B4:I4<MAX(B4:I4))))*($L$2:$Q$2))/SUM($L$2:$Q$2)
Dynamic array formulas.

This worked. Thank you very much.

@awoohaw, Thank you so much for your help. I'm a 20 year excel (Power?) user and feel like I have a good handle on how to do things, but you showed me several new formulas to play with. I do not want to order the Weighting by smallest to largest, but rather from Oldest to Newest, so @AhoyNC's formula works for me.

 

[awoohaw]

Jimminy Christmas, when you said first, middle, last I was thinking size. Wow. My bad.

I'm glad you got your solution!