Dynamically Changing QUARTILE Array When Value Changes

[bdbrew]

Using MS Office Professional Plus 2013

I have a table with data grouped by item #. I would like to use the formula below on each group with the ability to copy the formula down, and have the QUARTILE function reference each group separately.

=IF(OR(C2>(QUARTILE(C$2:C$13,3)+(1.5*(QUARTILE(C$2:C$13,3)-QUARTILE(C$2:C$13,1)))),C2<(QUARTILE(C$2:C$13,1)-(1.5*(QUARTILE(C$2:C$13,3)-QUARTILE(C$2:C$13,1))))),"Outlier","")

The formula above would be for the first group of items. There is a blank row between each group. The next group should have the same formula, but the QUARTILE array would change:

=IF(OR(C2>(QUARTILE(C$15:C$20,3)+(1.5*(QUARTILE(C$2:C$13,3)-QUARTILE(C$2:C$13,1)))) . . .

I assume some form of dynamic named range would be used in place of the QUARTILE array.

The number of groups, and therefore the number of rows can vary.
The number or rows per group will vary.

Sample Data:
The formula above would be in column D.
(the data is fabricated for example of layout and likely doesn't show real intended formula results.)

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Item#[/TD]
[TD]Description[/TD]
[TD]Percent[/TD]
[TD]Outlier[/TD]
[/TR]
[TR]
[TD]84838[/TD]
[TD]Bike Seat[/TD]
[TD="align: center"]7.36%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]84838[/TD]
[TD]Bike Seat[/TD]
[TD="align: center"]8.45%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]84838[/TD]
[TD]Bike Seat[/TD]
[TD="align: center"]36.65%[/TD]
[TD]Outlier[/TD]
[/TR]
[TR]
[TD]84838[/TD]
[TD]Bike Seat[/TD]
[TD="align: center"]14.10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]63233[/TD]
[TD]Front Wheel
[/TD]
[TD="align: center"]27.56%[/TD]
[TD]Outlier[/TD]
[/TR]
[TR]
[TD]63233[/TD]
[TD]Front Wheel[/TD]
[TD="align: center"]12.07%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]63233[/TD]
[TD]Front Wheel[/TD]
[TD="align: center"]11.47%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]77456[/TD]
[TD]Frame[/TD]
[TD="align: center"]6.25%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]77456[/TD]
[TD]Frame[/TD]
[TD="align: center"]5.76%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[XLearner]

Hi,

Assuming Item# are in A2:A101, Percent in C2:C101, in D2 and copy down try:

=IF($A2="","",IF(OR(
$C2 > (QUARTILE(IF($A$2:$A$101=$A2,$C$2:$C$101),3) +
      (1,5*(QUARTILE(IF($A$2:$A$101=$A2,$C$2:$C$101),3) - QUARTILE(IF($A$2:$A$101=$A2,$C$2:$C$101),1)))),
$C2 < (QUARTILE(IF($A$2:$A$101=$A2,$C$2:$C$101),1) -
      (1,5*(QUARTILE(IF($A$2:$A$101=$A2,$C$2:$C$101),3) - QUARTILE(IF($A$2:$A$101=$A2,$C$2:$C$101),1))))),"Outlier",""))

validate with Ctrl+Shift+Enter (array formula)

Regards
XLearner

 

[bdbrew]

Thanks for the reply XLearner. I will try this.

 

[bdbrew]

Sorry XLearner, but I couldn't get this to work. I keep getting the error "We found an error with this formula. . ."

 

[XLearner]

I don't run an EN version of Excel so have to translate the formulas. Checked the one I suggested and don't see any problem with it...
See SAMPLE_QuartileIf.xlsx

 

[bdbrew]

This does appear to work, however I must admit that I don't understand how.
Thanks.

 

[bdbrew]

So now how can I apply a dynamic named array so that I do not have to hard-code the final row number?

 

[XLearner]

Hi,

Define the following 3 Named variables (Ctrl+F3):
vLastRow =MAX(ISNUMBER($A$2:$A$1501)*ROW($A$2:$A$1501))
rngItems =INDIRECT(ADDRESS(ROW($A$2),COLUMN($A$2))&":"&ADDRESS(vLastRow,COLUMN($A$2)))
rngPourcentages =INDIRECT(ADDRESS(ROW($A$2),COLUMN($C$2))&":"&ADDRESS(vLastRow,COLUMN($C$2)))

and update the formula accordingly.

If 1501 is not enough adjust to your max expected number of rows but definitively avoid the full column (i.e. ROW($A:$A)) for perf. reasons

Regards
XLearner

 

[XLearner]

I must admit that I don't understand how

Take a basic sample:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Item[/TD]
[TD="align: center"]%age[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]84838<strike></strike>[/TD]
[TD="align: right"]7.36%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]84838<strike></strike><strike></strike>[/TD]
[TD="align: right"]8.45%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]63233<strike></strike>[/TD]
[TD="align: right"]27.56%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]63233<strike></strike>[/TD]
[TD="align: right"]12.7%[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]84838<strike></strike>[/TD]
[TD="align: right"]12.5%[/TD]
[/TR]
</tbody>[/TABLE]

In any cell enter =IF($A$2:$A$6=$A2,$B$2:$B$6) and do not hit Enter
Instead, highlight the complete formula in the formula bar and hit F9
you should see the following array: {0.0736,0.0845,FALSE;FALSE,0.125}

When this IF... is nested into your QUARTILE function, when the formula is calculated this "becomes"
QUARTILE({0.0736,0.0845,FALSE;FALSE,0.125},3)

Hope this helps

Regards
XLearner