I want to do median with multiple columns criteria.

If B2:B1000=2
AND
If C2:C1000=20006

QUARTILE(D2:D1000)

The below returns an error. What am I missing? Thanks in advance.

=QUARTILE(IF(AND(B$2:B$1000=2,C$2:C$1000=2006),D$2 :D$1000,1),0)

=QUARTILE(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2: D$1000),1)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jhicsupt" wrote in message
...
I want to do median with multiple columns criteria.

If B2:B1000=2
AND
If C2:C1000=20006

QUARTILE(D2:D1000)

The below returns an error. What am I missing? Thanks in advance.

=QUARTILE(IF(AND(B$2:B$1000=2,C$2:C$1000=2006),D$2 :D$1000,1),0)

Thanks - it worked perfectly!

"Bob Phillips" wrote:

=QUARTILE(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2: D$1000),1)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jhicsupt" wrote in message
...
I want to do median with multiple columns criteria.

If B2:B1000=2
AND
If C2:C1000=20006

QUARTILE(D2:D1000)

The below returns an error. What am I missing? Thanks in advance.

=QUARTILE(IF(AND(B$2:B$1000=2,C$2:C$1000=2006),D$2 :D$1000,1),0)

If criteria is not met, it returns a #NUM. How do I get it to return Nothing?

"Bob Phillips" wrote:

=QUARTILE(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2: D$1000),1)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jhicsupt" wrote in message
...
I want to do median with multiple columns criteria.

If B2:B1000=2
AND
If C2:C1000=20006

QUARTILE(D2:D1000)

The below returns an error. What am I missing? Thanks in advance.

=QUARTILE(IF(AND(B$2:B$1000=2,C$2:C$1000=2006),D$2 :D$1000,1),0)

=QUARTILE(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2: D$1000,0),1)

still array entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jhicsupt" wrote in message
...
If criteria is not met, it returns a #NUM. How do I get it to return
Nothing?

"Bob Phillips" wrote:

=QUARTILE(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2: D$1000),1)

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"jhicsupt" wrote in message
...
I want to do median with multiple columns criteria.

If B2:B1000=2
AND
If C2:C1000=20006

QUARTILE(D2:D1000)

The below returns an error. What am I missing? Thanks in advance.

=QUARTILE(IF(AND(B$2:B$1000=2,C$2:C$1000=2006),D$2 :D$1000,1),0)

I was using the sort of the same principle for MEDIAN. However, if value is
true, it's still coming up with 0. What am I doing wrong?

Thanks again.

=MEDIAN(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2:D$ 1000,0))

"Bob Phillips" wrote:

=QUARTILE(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2: D$1000,0),1)

still array entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jhicsupt" wrote in message
...
If criteria is not met, it returns a #NUM. How do I get it to return
Nothing?

"Bob Phillips" wrote:

=QUARTILE(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2: D$1000),1)

which is an array formula, it should be committed with Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"jhicsupt" wrote in message
...
I want to do median with multiple columns criteria.

If B2:B1000=2
AND
If C2:C1000=20006

QUARTILE(D2:D1000)

The below returns an error. What am I missing? Thanks in advance.

=QUARTILE(IF(AND(B$2:B$1000=2,C$2:C$1000=2006),D$2 :D$1000,1),0)

Isn't the zero result coming from all the zeros you are getting from the
exception side of your IF statement (i.e. when you don't meet the B and C
conditions)?

Will =MEDIAN(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2:D$ 1000,"")) work for
you (array-entered)?
--
David Biddulph

"jhicsupt" wrote in message
...
I was using the sort of the same principle for MEDIAN. However, if value
is
true, it's still coming up with 0. What am I doing wrong?

Thanks again.

=MEDIAN(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2:D$ 1000,0))

"Bob Phillips" wrote:

=QUARTILE(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2: D$1000,0),1)

still array entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"jhicsupt" wrote in message
...
If criteria is not met, it returns a #NUM. How do I get it to return
Nothing?

"Bob Phillips" wrote:

=QUARTILE(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2: D$1000),1)

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly
brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"jhicsupt" wrote in message
...
I want to do median with multiple columns criteria.

If B2:B1000=2
AND
If C2:C1000=20006

QUARTILE(D2:D1000)

The below returns an error. What am I missing? Thanks in advance.

=QUARTILE(IF(AND(B$2:B$1000=2,C$2:C$1000=2006),D$2 :D$1000,1),0)

=IF(SUM((B$2:B$1000=2)*(C$2:C$1000=2006))=0,0,MEDI AN(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2:D$1000) ))

as aver array-entered

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jhicsupt" wrote in message
...
I was using the sort of the same principle for MEDIAN. However, if value
is
true, it's still coming up with 0. What am I doing wrong?

Thanks again.

=MEDIAN(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2:D$ 1000,0))

"Bob Phillips" wrote:

=QUARTILE(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2: D$1000,0),1)

still array entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"jhicsupt" wrote in message
...
If criteria is not met, it returns a #NUM. How do I get it to return
Nothing?

"Bob Phillips" wrote:

=QUARTILE(IF((B$2:B$1000=2)*(C$2:C$1000=2006),D$2: D$1000),1)

which is an array formula, it should be committed with
Ctrl-Shift-Enter,
not
just Enter.
Excel will automatically enclose the formula in braces (curly
brackets),
do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"jhicsupt" wrote in message
...
I want to do median with multiple columns criteria.

If B2:B1000=2
AND
If C2:C1000=20006

QUARTILE(D2:D1000)

The below returns an error. What am I missing? Thanks in advance.

=QUARTILE(IF(AND(B$2:B$1000=2,C$2:C$1000=2006),D$2 :D$1000,1),0)

It looks like the formula you provided is almost correct, but there is a small mistake in the criteria for the AND function. Instead of "C$2:C$1000=2006", it should be "C$2:C$1000=20006" to match the criteria you mentioned in your question.

Here is the corrected formula:

1. =QUARTILE(IF(AND(B$2:B$1000=2,C$2:C$1000=20006),D$ 2:D$1000,1),0)

This formula uses the IF function to check if the values in columns B and C meet the specified criteria. If both conditions are true, it returns the corresponding value in column D. If either condition is false, it returns 1 (or any other value you choose).

The resulting array of values is then passed to the QUARTILE function, which calculates the median (or any other quartile you specify with the second argument).

Note that this is an array formula, so you need to press Ctrl+Shift+Enter instead of just Enter when entering it in the cell. Also, make sure that the range references (B$2:B$1000, C$2:C$1000, D$2:D$1000) match the actual range of your data.