Creating an Array using Percentile Formula

[xLBaron]

I was hoping that someone could help me out with this one. I want
create an excel formula with Percentile that uses an array. The length
of the data will vary but the Country names in Column A will always be
available.

Example:

Col (A) - Col (B)
Country - Sales

Australia - 100
Australia - 200
Australia - 300
Australia - 400
Australia - 500
Brazil - 600
Brazil - 700
Brazil - 800
Brazil - 900
Brazil - 1000
USA - 800
USA - 700
USA - 600
USA - 500
USA - 400

Using the formula "=percentile(array,.5)" how can a create an array
to seek out the country names in Col A to get the below results.

Australia - 300
Brazil - 800
USA - 600

Much thanks.

 

[Bob Phillips]

=PERCENTILE(IF(A2:A20="Australia",B2:B20),0.5)

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.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob


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

 

[xLBaron]

=PERCENTILE(IF(A2:A20="Australia",B2:B20),0.5)

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.

Note that you cannot use a whole column in array formulae (prior to excel
2007), but must use an explicit range.

--
---
HTH

Bob

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










- Show quoted text -

Bob -

You are too good ... Thanks!!!