Creating an Array using Percentile Formula

  • Thread starter Thread starter xLBaron
  • Start date Start date
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.
 
=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)
 
=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 -
Click to expand...



Bob -

You are too good ... Thanks!!!
 
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
 
Back
Top