Using Quartile function on data with multiple conditions

K

Kevyn

New Member
Joined
Jul 10, 2008
Messages
1
I am trying to find the 25% (1st Quartile) on a set of data . However, I want to get the quartile base on ID (I have 51 different IDs) and a second category that is "0" or "1".

The problem I am having when using the quartile function is that I get the quartile for the entire range of data (6700 lines) and not just the quartile for the data that meets my conditions.

How do I set up conditions on teh quartile function so that it only returns the quartile for data that matches condition 1 adn condition 2. For example, I want the quartile for ID = 25 and condition 1 (Not the quartile for all of ID = 25 or all the data).

Thanks in advance
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Hello Kevyn, welcome to MrExcel

If your IDs are in column A, second categorys in column B and values in C try this formula for Quartile 1

=QUARTILE(IF(A2:A6700=25,IF(B2:B6700=1,C2:C6700)),1)

This is an array formula which needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar
 
Upvote 0
barry houdini said:
Hello Kevyn, welcome to MrExcel

If your IDs are in column A, second categorys in column B and values in C try this formula for Quartile 1

=QUARTILE(IF(A2:A6700=25,IF(B2:B6700=1,C2:C6700)),1)

This is an array formula which needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar
Click to expand...


Hi,

New to this forum but have used it as reference in the past and have been most helpful. Thanks.

I have the same inquiry as above and tried the formula but it returns a #NUM!

Any help would be great!

Thanks.
 
Upvote 0
Check your criteria as Excel will return a #NUM if nothing matches in your criteria range.
 
Upvote 0
Thanks for replying AhoyNC. They're all in there. Not sure why it's not finding it.

Maybe you can suggest another formula. This one above is per quatrtile. I found one that returns the ranking with one formula =MATCH($G5,QUARTILE($G$5:$G$24,{4,3,2,1}),-1). I used that to rank the value of one group. If only it could be layered with the two other conditions I have. Column C has three markets and column D has 3 levels. G has the values. I need the quartile ranking per market per level. Any suggestions would be greatly appreciated :)

Thanks!
 
Upvote 0
It would help if you could show a small sample of what your data looks like. I assume you know that this is an array formula and needs to be entered with CTRL-SHIFT-ENTER (command-return on MAC).
 
Upvote 0
AhoyNC said:
It would help if you could show a small sample of what your data looks like. I assume you know that this is an array formula and needs to be entered with CTRL-SHIFT-ENTER (command-return on MAC).
Click to expand...


Yes, I enter with CTRL-SHIFT-ENTER. Where can i send the sample?
 
Upvote 0
Here's a sample:

[TABLE="width: 364"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Centre[/TD]
[TD]Level[/TD]
[TD]Total Deals[/TD]
[TD]Days[/TD]
[TD]Deals/Day[/TD]
[TD]Quartile Rank[/TD]
[/TR]
[TR]
[TD]WSBCC[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]3.1[/TD]
[TD="align: center"]#NUM![/TD]
[/TR]
[TR]
[TD]WSBCC[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]QSBCC[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]6.1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ESBCC[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]8.4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WSBCC[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]141[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]7.1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ESBCC[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1.8[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ESBCC[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]3.7[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WSBCC[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2.8[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]QSBCC[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]148[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]7.4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]QSBCC[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2.2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WSBCC[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1.8[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ESBCC[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]3.3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WSBCC[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2.8[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WSBCC[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0.1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ESBCC[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]146[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]7.3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ESBCC[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]74[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]3.7[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WSBCC[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]3.1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ESBCC[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2.9[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]QSBCC[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1.9[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WSBCC[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0.6[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]QSBCC[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1.4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ESBCC[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WSBCC[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2.7[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ESBCC[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2.9[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ESBCC[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1.5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WSBCC[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]3.9[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ESBCC[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2.2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]QSBCC[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0.1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ESBCC[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2.8[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Something like this?
Excel Workbook
CDEFGH
1CentreLevelTotal DealsDaysDeals/DayQuartile Rank
2WSBCC762203.12
3WSBCC7802043
4QSBCC7121206.11
5ESBCC7168208.44
6WSBCC7141207.14
7ESBCC736201.81
8ESBCC774203.73
9WSBCC755202.81
10QSBCC7148207.44
11QSBCC844202.24
12WSBCC836201.83
13ESBCC866203.32
14WSBCC856202.84
15WSBCC82200.11
16ESBCC8146207.34
17ESBCC874203.73
18WSBCC861203.14
19ESBCC858202.91
20QSBCC837201.93
21WSBCC812200.62
22QSBCC827201.41
23ESBCC9202011
24WSBCC954202.71
25ESBCC957202.94
26ESBCC930201.52
27WSBCC978203.94
28ESBCC943202.23
29QSBCC92200.14
30ESBCC956202.84
Sheet
 
Upvote 0
You must log in or register to reply here.

Similar threads

Sangar_338
  • Solved
Methods for Quartile Calculation with Multiple Criteria
Replies
5
Views
725
Fluff
Fluff
X
  • Question Question
How To Use Quartile Function Conditionally?
Replies
1
Views
1K
Sangar_338
Sangar_338
O
  • Question Question
Quartile for each row of data
Replies
0
Views
574
olive2403
O
K
  • Question Question
QUARTILE formula help needed
Replies
1
Views
730
KRice
K
B
  • Question Question
Conditional Formatting of cells within a table using X-Lookup to look for values from a range in a second table.
Replies
1
Views
176
sijpie
sijpie

Forum statistics

Threads
1,223,283
Messages
6,171,176
Members
452,388
Latest member
Lorenzo_Barry

Share this page

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