percentile - data in 2 different ranges
 

I need to calculate a percentile, but my data is not organized as one single
array, but in 2 ranges.

I've tried things like:
=+PERCENTILE({A1:A4,D8:D11},0.9)
=+PERCENTILE({A1:A4;D8:D11},0.9)
=+PERCENTILE(A1:A4&D8:D11,0.9)
={+PERCENTILE(A1:A4&D8:D11,0.9)}

Nothing seems to work... Any ideas?

Thanks

Answer: percentile - data in 2 different ranges
 

To calculate a percentile for data in two different ranges, use the following formula:

Example:

Steps:

1. Enter the formula in the cell where you want to display the result.
2. Replace "range1" and "range2" with the cell ranges that contain the data you want to calculate the percentile for.
3. Replace "percentile" with the percentile value you want to calculate (e.g. 0.9 for the 90th percentile).
4. Make sure to separate the two ranges with a semicolon (;) inside curly braces {}.
5. Ensure that the ranges have the same number of columns to avoid errors.

percentile - data in 2 different ranges
 

In article ,
Idoia wrote:

I need to calculate a percentile, but my data is not organized as one single
array, but in 2 ranges.

I've tried things like:
=+PERCENTILE({A1:A4,D8:D11},0.9)
=+PERCENTILE({A1:A4;D8:D11},0.9)
=+PERCENTILE(A1:A4&D8:D11,0.9)
={+PERCENTILE(A1:A4&D8:D11,0.9)}

Nothing seems to work... Any ideas?

Thanks

Try...

=PERCENTILE((A1:A4,D8:D11),0.9)

--
Domenic
http://www.xl-central.com

percentile - data in 2 different ranges
 

Thanks, I seem to have tried everything but the easy way...

Now a bit more difficult: what if my range is in a different sheet in the
same book? The ranges are located in a different place in each sheet, so 3D
references won't work?

Your formula now becomes
=+PERCENTILE((A1:A4,Sheet2!D8:D11),0.9)

But stops working

Thanks a lot

"Domenic" wrote:

In article ,
Idoia wrote:

I need to calculate a percentile, but my data is not organized as one single
array, but in 2 ranges.

I've tried things like:
=+PERCENTILE({A1:A4,D8:D11},0.9)
=+PERCENTILE({A1:A4;D8:D11},0.9)
=+PERCENTILE(A1:A4&D8:D11,0.9)
={+PERCENTILE(A1:A4&D8:D11,0.9)}

Nothing seems to work... Any ideas?

Thanks

Try...

=PERCENTILE((A1:A4,D8:D11),0.9)

--
Domenic
http://www.xl-central.com

percentile - data in 2 different ranges
 

Someone else can hopefully answer your question, but what I can tell you is
that you don't need the + signs. A Excel formula starts with =, not with
=+. The + is a relic from old Lotus spreadsheets.
--
David Biddulph

"Idoia" wrote in message
...
I need to calculate a percentile, but my data is not organized as one
single
array, but in 2 ranges.

I've tried things like:
=+PERCENTILE({A1:A4,D8:D11},0.9)
=+PERCENTILE({A1:A4;D8:D11},0.9)
=+PERCENTILE(A1:A4&D8:D11,0.9)
={+PERCENTILE(A1:A4&D8:D11,0.9)}

Nothing seems to work... Any ideas?

Thanks

percentile - data in 2 different ranges
 

In article ,
Idoia wrote:

Thanks, I seem to have tried everything but the easy way...

Now a bit more difficult: what if my range is in a different sheet in the
same book? The ranges are located in a different place in each sheet, so 3D
references won't work?

Your formula now becomes
=+PERCENTILE((A1:A4,Sheet2!D8:D11),0.9)

But stops working

Thanks a lot

If each range references a single column and the ranges are the same
size, try...

=PERCENTILE(CHOOSE({1,2},A1:A4,Sheet2!D8:D11),0.9)

--
Domenic
http://www.xl-central.com