combining Trimmean and quartile function together

[channguyen]

Is it possible to combine the trimmean function with the quartile function?

I'm trying to take off the top and bottom 10% before calculating the quartiles.

[FlameRetired]

I'm guessing yes. I would like more specifics though.

Do you want a formula/demonstration of how? A sample workbook upload might help.

[channguyen]

I would like a formula. Sorry I'm unable to upload. So i'll just write it out here

Product Price Per User
Item 1 $-
Item 2 $1.00
Item 3 $2.00
Item 4 $4.00
Item 5 $5.00
Item 6 $6.00
Item 7 $7.00
Item 8 $8.00
Item 9 $9.00
Item 10 $10.00
Item 11 $11.00
Item 12 $12.00
Item 13 $13.00
Item 14 $14.00
Item 15 $15.00
Item 16 $9,000.00

Based on that the current quartile would be

Without Trim 10 % trim
Min $-
Q1 $4.75
Q2 $8.50
Q3 $12.25
Max $9,000.00

What i'm trying to accomplish is the quartile after the 10% (5% on top and %5 on bottom) trim from that data set. Here's what the quartile would look like now

With 10% trim
Min $1.00
Q1 $5.25
Q2 $8.50
Q3 $11.75
Max $15.00

[channguyen]

I would like a formula. Sorry I'm unable to upload. So i'll just write it out here

Product Price Per User
Item 1 $-
Item 2 $1.00
Item 3 $2.00
Item 4 $4.00
Item 5 $5.00
Item 6 $6.00
Item 7 $7.00
Item 8 $8.00
Item 9 $9.00
Item 10 $10.00
Item 11 $11.00
Item 12 $12.00
Item 13 $13.00
Item 14 $14.00
Item 15 $15.00
Item 16 $9,000.00

Based on that the current quartile would be

Without Trim 10 % trim
Min $-
Q1 $4.75
Q2 $8.50
Q3 $12.25
Max $9,000.00

What i'm trying to accomplish is the quartile after the 10% (5% on top and %5 on bottom) trim from that data set. Here's what the quartile would look like now

With 10% trim
Min $1.00
Q1 $5.25
Q2 $8.50
Q3 $11.75
Max $15.00

[JeteMc]

Perhaps this will help.
A helper column, which may be moved and/or hidden for aesthetic purposes, keeps track of which values will be included. The helper column is populated using:

Please Login or Register  to view this content.

Note that cell E1 contains the percentage of trim.
The array entered formula* that populates the "w/ trim" column is: =QUARTILE(IF(C$2:C$21=TRUE,B$2:B$21),ROW(1:1)-1)
*An array entered formula is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
For future reference the way to attach a sample workbook is to click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

[FlameRetired]

Another way.
Try array entering this formula as below in D1 and fill down.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

	A	B	C	D
1	Item 1	$0.00	Min	$1.00
2	Item 2	$1.00	Q1	$5.25
3	Item 3	$2.00	Q2	$8.50
4	Item 4	$4.00	Q3	$11.75
5	Item 5	$5.00	Max	$15.00
6	Item 6	$6.00
7	Item 7	$7.00
8	Item 8	$8.00
9	Item 9	$9.00
10	Item 10	$10.00
11	Item 11	$11.00
12	Item 12	$12.00
13	Item 13	$13.00
14	Item 14	$14.00
15	Item 15	$15.00
16	Item 16	$9,000.00