Quartile Function and Arrays Question

Question

I need some help with the Quartile function and Arrays. I have 4 values that I need to find the 75th percentile from. The problem is that the Quartile function seems to be searching for cell address or something instead of raw value.

=QUARTILE((

IFERROR(VLOOKUP($A4,Breakdown2!$A$166:$W$189,B$1+1,FALSE),0)

,IFERROR(VLOOKUP($A4,Breakdown2!$A$341:$W$364,B$1+1,FALSE),0)

,IFERROR(VLOOKUP($A4,Breakdown2!$A$516:$W$532,B$1+1,FALSE),0)

,IFERROR(VLOOKUP($A4,Breakdown2!$A$663:$W$686,B$1+1,FALSE),0)

),3)

Due to reasons I need to find the 4 values so I don't necessarily have the exact cell reference . Each

IFERROR(VLOOKUP($A4,Breakdown2!$A$166:$W$189,B$1+1,FALSE),0)

is basically a raw value. So in essence, my function is trying to do this.

=QUARTILE((1,2,3,4)),3)

Overall, how do I turn (1,2,3,4) in to an array that Quartile can use?

Answer 1

Lorenz wrote:

my function is trying to do this.

=QUARTILE((1,2,3,4)),3)

Overall, how do I turn (1,2,3,4) in to an array that Quartile can use?

---

If you had 4 constants, you would write:

=QUARTILE({4,2,1,3},3)

But note that the result is 3.25, not 3.  If you are really interested in finding the second largest or third smallest, you would write:

=SMALL({4,2,1,3},3)

or

=LARGE({4,2,1,3},2)

Note the curly braces around the constant array. But that syntax works only for constants.

You don't have constants. Instead, you have expressions of the form IFERROR(VLOOKUP(....),.0).  In that case, you might write something like the following:

=QUARTILE(CHOOSE({1,2,3,4}, VLOOKUP("a",$C$1:$D$4,2,0), VLOOKUP("b",$C$1:$D$4,2,0),

VLOOKUP("c",$C$1:$D$4,2,0), VLOOKUP("d",$C$1:$D$4,2,0)),3)

That formula can be normally-entered (press Enter as usual) because Excel recognizes {1,2,3,4} as an array.

(In my example, C1:C4 has the letters "a", "b", "c" and "d" in random order, and D1:D4 has the corresponding numbers 1, 2, 3 and 4.)

Answer 2

Like Lorenz, I learnt a lot from this. Many thanks for clever use of CHOOSE with array

Bernard

Answer 3

Thank you so much.  With your post, I was able to solve my problem AND learn something new.

Answer 4

You formula should result in =QUARTILE({1,2,3,4},3) not =QUARTILE((1,2,3,4),3)

But I was unable to get Excel to accept this

=QUARTILE({VLOOKUP($A4,Breakdown2!$A$1:$D$6,B$1+1,FALSE) ,VLOOKUP($A4,Breakdown2!$A$1:$D$6,B$1+1,FALSE) ,VLOOKUP($A4,Breakdown2!$A$1:$D$6,B$1+1,FALSE) ,VLOOKUP($A4,Breakdown2!$A$1:$D$6,B$1+1,FALSE)},3)

I can get an answer using four cells for the values - see snip

best wishes