Removing outliers (Min/Max) within sumproduct array

[ExcelUser18]

Good Afternoon All,

I've tried effortlessly to figure out this equation and many other posts I see do not exactly fit my needs. Apologies if this has been repeated somewhere.


Column A has square footage (SF) numbers, some which are unavailable and contain zeros. Column B has $/square foot ($/SF), some which are also zero because the square footage is zero.

[TABLE="width: 126"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]SF[/TD]
[TD="align: center"]$/SF[/TD]
[/TR]
[TR]
[TD="align: center"]675[/TD]
[TD="align: center"]$250[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]$0[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]$0[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]$0[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]$0[/TD]
[/TR]
[TR]
[TD="align: center"]850[/TD]
[TD="align: center"]$340[/TD]
[/TR]
[TR]
[TD="align: center"]775[/TD]
[TD="align: center"]$387[/TD]
[/TR]
[TR]
[TD="align: center"]850[/TD]
[TD="align: center"]$364[/TD]
[/TR]
[TR]
[TD="align: center"]950[/TD]
[TD="align: center"]$342[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]$0[/TD]
[/TR]
[TR]
[TD="align: center"]900[/TD]
[TD="align: center"]$376[/TD]
[/TR]
[TR]
[TD="align: center"]775[/TD]
[TD="align: center"]$450[/TD]
[/TR]
[TR]
[TD="align: center"]950[/TD]
[TD="align: center"]$367[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6,725[/TD]
[TD="align: center"]$361[/TD]
[/TR]
</tbody>[/TABLE]

The weighted average for the above data is $361/SF. Easy enough. What I need to do or like to do is exclude the lowest and highest values from Column B ($/SF) AND also exclude its corresponding data in Column A (SF). Additionally, the MIN will calculate the first zero in there, but still returning a zero for the minimum because there are a total of 5 zeros in Column B. I know it's easier to delete those data points that have zeros but there are other columns attached to those data points and cannot be delete. Maybe, somehow, incorporate =small(B2:B14,countif(B2:B14,0)+1) as part of the MIN portion of the entire formula? This formula by itself ignores all the zero's and finds me the true minimum, but obviously I believe I need a more complex formula.

You guys are the experts, please feel free to ask questions. Many thanks.

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1	SF	$/SF	Rank	362.1374	13	6
2	675	250.00	6
3	0	0.00	1
4	0	0.00	1
5	0	0.00	1
6	0	0.00	1
7	850	340.00	7
8	775	387.00	12
9	850	364.00	9
10	950	342.00	8
11	0	0.00	1
12	900	376.00	11
13	775	450.00	13
14	950	367.00	10
15
16	T10_1705b	1b
1b

Cell Formulas
Range	Formula
D1	=SUMPRODUCT(--(C2:C14>F1),--(C2:C14),A2:A14,B2:B14)/SUMPRODUCT(--(C2:C14>6),--(C2:C14<13),A2:A14)
E1	=LARGE(C2:C14,1)
F1	=SMALL(C2:C14,COUNTIF(C2:C14,1)+1)
C2	=RANK(B2,$B$2:$B$14,1)

 

[ExcelUser18]

Excel 2010

	A	B	C	D	E	F
T10_1705b	1b

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #C0C0C0, align: center"]SF[/TD]
[TD="bgcolor: #C0C0C0, align: center"]$/SF[/TD]
[TD="bgcolor: #F2F6F8"]Rank[/TD]
[TD="align: right"]362.1374[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #F2F6F8, align: right"]675[/TD]
[TD="bgcolor: #F2F6F8, align: right"]250.00 [/TD]
[TD="bgcolor: #FFFF00, align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #F2F6F8, align: right"]0[/TD]
[TD="bgcolor: #F2F6F8, align: right"]0.00 [/TD]
[TD="bgcolor: #FFFF00, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #F2F6F8, align: right"]0[/TD]
[TD="bgcolor: #F2F6F8, align: right"]0.00 [/TD]
[TD="bgcolor: #FFFF00, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #F2F6F8, align: right"]0[/TD]
[TD="bgcolor: #F2F6F8, align: right"]0.00 [/TD]
[TD="bgcolor: #FFFF00, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #F2F6F8, align: right"]0[/TD]
[TD="bgcolor: #F2F6F8, align: right"]0.00 [/TD]
[TD="bgcolor: #FFFF00, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #F2F6F8, align: right"]850[/TD]
[TD="bgcolor: #F2F6F8, align: right"]340.00 [/TD]
[TD="bgcolor: #FFFF00, align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #F2F6F8, align: right"]775[/TD]
[TD="bgcolor: #F2F6F8, align: right"]387.00 [/TD]
[TD="bgcolor: #FFFF00, align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #F2F6F8, align: right"]850[/TD]
[TD="bgcolor: #F2F6F8, align: right"]364.00 [/TD]
[TD="bgcolor: #FFFF00, align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #F2F6F8, align: right"]950[/TD]
[TD="bgcolor: #F2F6F8, align: right"]342.00 [/TD]
[TD="bgcolor: #FFFF00, align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #F2F6F8, align: right"]0[/TD]
[TD="bgcolor: #F2F6F8, align: right"]0.00 [/TD]
[TD="bgcolor: #FFFF00, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #F2F6F8, align: right"]900[/TD]
[TD="bgcolor: #F2F6F8, align: right"]376.00 [/TD]
[TD="bgcolor: #FFFF00, align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #F2F6F8, align: right"]775[/TD]
[TD="bgcolor: #F2F6F8, align: right"]450.00 [/TD]
[TD="bgcolor: #FFFF00, align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #F2F6F8, align: right"]950[/TD]
[TD="bgcolor: #F2F6F8, align: right"]367.00 [/TD]
[TD="bgcolor: #FFFF00, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

1b​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=SUMPRODUCT(--(C2:C14>F1),--(C2:C14<E1),A2:A14,B2:B14)/SUMPRODUCT(--(C2:C14>6),--(C2:C14<13),A2:A14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]=LARGE(C2:C14,1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F1[/TH]
[TD="align: left"]=SMALL(C2:C14,COUNTIF(C2:C14,1)+1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=RANK(B2,$B$2:$B$14,1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I think that works. You guys are unreal, I have so much to learn. I'm going to play around with it a bit to make sure it traps what I need to do but thank you all for your help. Not sure how I even managed to create models in excel prior to this website.

 

[Dave Patton]

A version that is a little cleaner follows.

Excel 2010
	A	B	C	D	E
1	SF	$/SF	Include		362.14
2	675	250.00	FALSE
3	0	0.00	FALSE
4	0	0.00	FALSE
5	0	0.00	FALSE
6	0	0.00	FALSE
7	850	340.00	TRUE
8	775	387.00	TRUE
9	850	364.00	TRUE
10	950	342.00	TRUE
11	0	0.00	FALSE
12	900	376.00	TRUE
13	775	450.00	FALSE
14	950	367.00	TRUE
15
16	Weighted Average	T10_1705b	1bbb
1bbb

Cell Formulas
Range	Formula
E1	=SUMPRODUCT(--(C2:C14=TRUE),A2:A14,B2:B14)/SUMIF(C2:C14,TRUE,A2:A14)
C2	=AND(B2>SMALL($B$2:$B$14,COUNTIF($B$2:$B$14,0)+1),B2<(LARGE($B$2:$B$14,1)))

 

[ExcelUser18]

A version that is a little cleaner follows.


Excel 2010

	A	B	C	D	E
Weighted Average	T10_1705b	1bbb

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #EEECE1, align: center"]SF[/TD]
[TD="bgcolor: #EEECE1, align: center"]$/SF[/TD]
[TD="bgcolor: #EEECE1, align: center"]Include[/TD]
[TD="align: right"][/TD]
[TD="align: right"]362.14[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF, align: right"]675[/TD]
[TD="bgcolor: #FFFFFF, align: right"]250.00 [/TD]
[TD="bgcolor: #FFFFFF, align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0.00 [/TD]
[TD="bgcolor: #FFFFFF, align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0.00 [/TD]
[TD="bgcolor: #FFFFFF, align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0.00 [/TD]
[TD="bgcolor: #FFFFFF, align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0.00 [/TD]
[TD="bgcolor: #FFFFFF, align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FFFFFF, align: right"]850[/TD]
[TD="bgcolor: #FFFFFF, align: right"]340.00 [/TD]
[TD="bgcolor: #FFFFFF, align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FFFFFF, align: right"]775[/TD]
[TD="bgcolor: #FFFFFF, align: right"]387.00 [/TD]
[TD="bgcolor: #FFFFFF, align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FFFFFF, align: right"]850[/TD]
[TD="bgcolor: #FFFFFF, align: right"]364.00 [/TD]
[TD="bgcolor: #FFFFFF, align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFFFFF, align: right"]950[/TD]
[TD="bgcolor: #FFFFFF, align: right"]342.00 [/TD]
[TD="bgcolor: #FFFFFF, align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0.00 [/TD]
[TD="bgcolor: #FFFFFF, align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #FFFFFF, align: right"]900[/TD]
[TD="bgcolor: #FFFFFF, align: right"]376.00 [/TD]
[TD="bgcolor: #FFFFFF, align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #FFFFFF, align: right"]775[/TD]
[TD="bgcolor: #FFFFFF, align: right"]450.00 [/TD]
[TD="bgcolor: #FFFFFF, align: right"]FALSE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #FFFFFF, align: right"]950[/TD]
[TD="bgcolor: #FFFFFF, align: right"]367.00 [/TD]
[TD="bgcolor: #FFFFFF, align: right"]TRUE[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

1bbb​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E1[/TH]
[TD="align: left"]=SUMPRODUCT(--(C2:C14=TRUE),A2:A14,B2:B14)/SUMIF(C2:C14,TRUE,A2:A14)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=AND(B2>SMALL($B$2:$B$14,COUNTIF($B$2:$B$14,0)+1),B2<(LARGE($B$2:$B$14,1)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This works as well. I like the "Rank" version though as it gives me a good visual of where the numbers are distributed amount the lowest/highest. Thanks again for solving this problem, I use this CONSTANTLY in my profession.