Creating an Array using Percentile Formula

X

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.
 
B

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)
 
X

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!!!
 
Joined
Aug 24, 2009
Messages
1
Reaction score
0
Follow-Up to This Question

This formula works great, but how can I achieve the same result, with the same type of data (using an if statement) across several tabs?

For example, if I wanted to find 90% for the US for the below dataset how would I do that?

TAB 1 (two columns A/B)
Spain 100
France 200
Spain 50
US 10
US 60

TAB 2 (two columns A/B)
Spain 60
France 240
Spain 59
US 11
US 61
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top