Interquartile Average with IF Statement

zanderaruba

New Member
Joined
Jun 29, 2012
Messages
21
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
ABCD
1DollarsAverage
22660272
31380
42070
52751
62551
72091
82491
91861
10531
111661
122260
132460
141810
151301
16960
171710
182540
192861
201610
CTRL-SHIFT-ENTER.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top