Interquartile Average with IF Statement

[zanderaruba]

Hey guys,

I am looking to calculate the average within a certain quartile within a large data set. However, rather than just taking the average of the quartile of all my data, I want to also subject the formula to another condition.

For example, I have a set of $ values in column A. I then have a set of either “1” or “0”s in Column B. I would like to take the average of all the dollar values in Column A that have a 1 as their value in Column B and fall within the top quartile of those specific rows.

Can anyone help me out with a formula that could be used to accomplish this? Thank you

 

[AhoyNC]

Maybe something like this.
In this example it's taking the upper quartile of the data with 1 in column B and then the average which I think is what you were asking. If you wanted to first find the upper quartile of all the data (both 0 & 1 from column B) and then only average those numbers with a "1" we would need to make some changes.

This is an array formula and must be entered with

Excel Workbook
	A	B	C	D
1	Dollars			Average
2	266	0		272
3	138	0
4	207	0
5	275	1
6	255	1
7	209	1
8	249	1
9	186	1
10	53	1
11	166	1
12	226	0
13	246	0
14	181	0
15	130	1
16	96	0
17	171	0
18	254	0
19	286	1
20	161	0
CTRL-SHIFT-ENTER.