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%​

 

[DavCode]

Hi there,

I thought i'd have a go at this. First, I highlighted the max and min values in each row using Conditional Formatting (you may not need this).

Second, I generated a number associated with each cell in each row of data. Max and min values are 'blanked' and the rest are counted from 1 to 6 in order from left to right. The trick here was to use a dynamic range when looking for the maximum number already found to the left of the number being checked. Note that the first column formula is different from the remaining column formulae.

Thirdly, i used a 'sumif' formula to add together the components of each of your groups, i.e. most recent 2 weeks, etc, and multiplied them by the weighting percentages.
Then sum the weighted totals of the groups to get the weighted average of each row of data (excluding max/min values).

Obviously, you need to be careful when entering brackets and $ in the formulas. All formulae and methods are posted in the attached file.

Please let me know if you have any issues.

Regards,
DavCode

 

[awoohaw]

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%​

WHAT version of excel are you using? Please update your profile so the forum can see what version of excel you use.
Thanks!

 

[awoohaw]

if you have 365, try this:
(Sorry, I didnt format the reslt as percentage, but I think you get the idea.)

mr excel questions 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
9
10		Wk5	Wk6	Wk7	Wk8	Wk9	Wk10	Wk11	Wk12
11	36316	55%	13%	19%	33%	22%	22%	45%	22%			0.3020
12	36536	13%	17%	27%	29%	-3%	19%	18%	10%			0.1905
13	36571	5%	-2%	-4%	-5%	6%	-1%	-3%	-4%			-0.0055
14
cmfuld

Cell Formulas
Range	Formula
L11:L13	L11	=SUM(DROP(DROP(SORT(B11:I11,1,1,TRUE),,-1),,1)*TEXTSPLIT(TEXTJOIN(",",,0.1,0.1,0.15,0.15,0.25,0.25),","))

 

[cmfuld]

WHAT version of excel are you using? Please update your profile so the forum can see what version of excel you use.
Thanks!

Sorry. I am using Excel 365 for Enterprise.

 

[Fluff]

I am using Excel 365 for Enterprise.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[awoohaw]

if you have 365, try this:
(Sorry, I didnt format the reslt as percentage, but I think you get the idea.)

mr excel questions 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
9
10		Wk5	Wk6	Wk7	Wk8	Wk9	Wk10	Wk11	Wk12
11	36316	55%	13%	19%	33%	22%	22%	45%	22%			0.3020
12	36536	13%	17%	27%	29%	-3%	19%	18%	10%			0.1905
13	36571	5%	-2%	-4%	-5%	6%	-1%	-3%	-4%			-0.0055
14
cmfuld

Cell Formulas
Range	Formula
L11:L13	L11	=SUM(DROP(DROP(SORT(B11:I11,1,1,TRUE),,-1),,1)*TEXTSPLIT(TEXTJOIN(",",,0.1,0.1,0.15,0.15,0.25,0.25),","))

Does your 365 enterprise have DROP, SORT, TEXTJOIN, and TEXTSPLIT?

 

[awoohaw]

try this.

=SUM(SMALL(B18:I18,COLUMN(N18:S18)-12)*{0.1,0.1,0.15,0.15,0.25,0.25})

and copy down.

You can't just type the above and it will work. In the above the first formula is in cell N18. If you put it anywhere else you must change the column subtraction. So, if you start in column D, the column formula portion would be COLUMN(D18:I18)-2). This is so you can send the values 2 through 6 to the [k] part of the SMALL function. It is an array formula, so you ma y need to enter the formula with the CSE (CNTL-SHFT-ENTER) keyboard combination. (but since you are 365 something that may not be necessary).

 

[awoohaw]

here is the above in column L:

mr excel questions 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
17		Wk5	Wk6	Wk7	Wk8	Wk9	Wk10	Wk11	Wk12
18	36316	55%	13%	19%	33%	22%	22%	45%	22%			0.3020
19	36536	13%	17%	27%	29%	-3%	19%	18%	10%			0.1905
20	36571	5%	-2%	-4%	-5%	6%	-1%	-3%	-4%			-0.0055
cmfuld

Cell Formulas
Range	Formula
L18:L20	L18	=SUM(SMALL(B18:I18,COLUMN(L18:Q18)-10)*({0.1,0.1,0.15,0.15,0.25,0.25}))

 

[cmfuld]

Does your 365 enterprise have DROP, SORT, TEXTJOIN, and TEXTSPLIT?

Thanks for all the help. We do have those formulas.

My goal would be to do all of the calculations in one cell if I can, which is the Weighted Average in my example.

I like the e Sum(Drop(Drop formula, but it doesn't seem to be giving me the right number. When I manually put the weekly numbers in (dropping High and Low), and then do a Sumproduct formula, I get 28.5% not 30.2% for the first line. The Sum(DropDrop portion of the formula does give me the right %s (162.63 -Sum of the columns from 18.6 to 21.8). Its the TextSplit/TextJoin that is not multiplying correctly.

Here is how the weighting should work for the numbers I provided.

Weighting​

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

Wtd Avg

Sum Drop Drop

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%​

36536​

13%​

17%​

27%​

29%​

-3%​

19%​

18%​

10%​

17.2%​

-3%​

29%​

13%​

17%​

27%​

19%​

18%​

10%​

16.8%​

18.9%​

36571​

5%​

-2%​

-4%​

-5%​

6%​

-1%​

-3%​

-4%​

-1.5%​

-5%​

6%​

5%​

-2%​

-4%​

-1%​

-3%​

-4%​

-2.2%​

-0.5%​

36316​

1.9%​

3.3%​

3.2%​

3.3%​

11.3%​

5.4%​

28.5%​

36536​

1.3%​

1.7%​

4.1%​

2.8%​

4.4%​

2.6%​

16.8%​

36571​

0.5%​

-0.2%​

-0.6%​

-0.1%​

-0.8%​

-0.9%​

-2.2%​