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.

 

[DonnyF]

Bump

 

[Aladin Akyurek]

What would be the formula if you did not have any 0's or empty cells anywhere?

 

[Nine Zero]

Hello,

Try putting this formula where ever you want your result

=(SUMIF(B:B,">"&SMALL(B:B,1),B:B))/COUNTIF(B:B,">"&SMALL(B:B,1))

This will average out only the values grater than your lowest value which also excludes them when dividing so instead of diving by say 13 value which include 5 zeros it would only include 8 values

 

[ExcelUser18]

What would be the formula if you did not have any 0's or empty cells anywhere?

That's the $1Million question, i'm not sure?

 

[ExcelUser18]

Hello,

Try putting this formula where ever you want your result

=(SUMIF(B:B,">"&SMALL(B:B,1),B:B))/COUNTIF(B:B,">"&SMALL(B:B,1))

This will average out only the values grater than your lowest value which also excludes them when dividing so instead of diving by say 13 value which include 5 zeros it would only include 8 values

Just to be clear, this would be the portion of the formula to exclude the zero's when finding the Minimum? I would still then need to remove the Min/Max from this column THEN apply that to the sumproduct formula to get the weighted average without the zeros/min/max.

 

[Aladin Akyurek]

That's the $1Million question, i'm not sure?

Just suppose that the are no 0's and no blanks: What would be the SumProduct you hae apparently in mind?

 

[ExcelUser18]

Just suppose that the are no 0's and no blanks: What would be the SumProduct you hae apparently in mind?

It would be =sumproduct((columnA-Min(columnA)-max(columnA),columnB-min(columnB)-max(columnB))/(sum(columnA)-min(columnA)-max(columnA))

Does that make sense or answer your question?

 

[AhoyNC]

Does this work for you?

Excel Workbook
	A	B	C
1	SF	$/SF
2	675	$250	349.416
3	0	$0
4	0	$0
5	0	$0
6	0	$0
7	850	$340
8	775	$387
9	850	$364
10	950	$342
11	0	$0
12	900	$376
13	775	$450
14	950	$367
Sheet

 

[ExcelUser18]

Does this work for you?

*	A	B	C
*
*
*
*
*
*
*
*
*
*
*
*
*

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]SF[/TD]
[TD="bgcolor: #c0c0c0, align: center"]$/SF[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]675[/TD]
[TD="align: right"]$250 [/TD]
[TD="bgcolor: #ffff00, align: right"]349.416[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$0 [/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$0 [/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$0 [/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$0 [/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]850[/TD]
[TD="align: right"]$340 [/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]775[/TD]
[TD="align: right"]$387 [/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]850[/TD]
[TD="align: right"]$364 [/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]950[/TD]
[TD="align: right"]$342 [/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]$0 [/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]$376 [/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]775[/TD]
[TD="align: right"]$450 [/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]950[/TD]
[TD="align: right"]$367 [/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C2 =SUMPRODUCT(--(B2:B14<>MIN(B2:B14)),--(B2:B14<>MAX(B2:B14)),B2:B14,A2:A14)/SUMPRODUCT(--(B2:B14<>MIN(B2:B14)),--(B2:B14<>MAX(B2:B14)),A2:A14) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hi Ahoy,

I will try to check your formula now but if the yellow box is your answer, I'm afraid it's not what I am looking for. Removing $250 and $450 as the min/max and its corresponding square footage from =sumproduct(ColumnB,ColumnA)/sum(ColumnA(not summing 675 SF and 775 SF), the weighted average would be approximately $362/SF. In my chart below Column A has a sum of, excluding zeros/min/max of 5,275 SF. Column C "Price" is the product of the SF multiplied by the $/SF. The lowest $/SF, $250 and the highest, $450, are voided. The remaining 6 data points are then summed up for a total of $1,910,000. Dividing that number by the total SF of 5,275 SF, I get a weighted average, net of the min/max calculations (while also excluding the zero's within the min calculation).

[TABLE="width: 201"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]SF[/TD]
[TD]$/SF[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]675[/TD]
[TD]$250[/TD]
[TD]Void[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]850[/TD]
[TD]$340[/TD]
[TD]$289,000[/TD]
[/TR]
[TR]
[TD]775[/TD]
[TD]$387[/TD]
[TD]$300,000[/TD]
[/TR]
[TR]
[TD]850[/TD]
[TD]$364[/TD]
[TD]$309,000[/TD]
[/TR]
[TR]
[TD]950[/TD]
[TD]$342[/TD]
[TD]$325,000[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]$0[/TD]
[TD]$0[/TD]
[/TR]
[TR]
[TD]900[/TD]
[TD]$376[/TD]
[TD]$338,000[/TD]
[/TR]
[TR]
[TD]775[/TD]
[TD]$450[/TD]
[TD]Void[/TD]
[/TR]
[TR]
[TD]950[/TD]
[TD]$367[/TD]
[TD]$349,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5,275[/TD]
[TD][/TD]
[TD="align: right"]$1,910,000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]$362[/TD]
[/TR]
</tbody>[/TABLE]