Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 29
Default 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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: percentile - data in 2 different ranges

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

Formula:
=PERCENTILE({range1;range2}, percentile
Example:

Formula:
=PERCENTILE({A1:A4;D8:D11}, 0.9
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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 265
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 29
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default 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





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 265
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Display Percentile BLR Excel Worksheet Functions 6 December 4th 08 11:21 PM
Use excel to calculate a percentile from data in multiple sheets EmyG Excel Worksheet Functions 0 July 24th 07 03:40 AM
to get its k-value from percentile and array Peter Excel Worksheet Functions 3 March 22nd 07 03:46 PM
Returning all data from a list above the nth percentile chlor Excel Worksheet Functions 11 February 22nd 06 02:28 PM
percentile Blessingspoint Excel Worksheet Functions 2 January 22nd 05 07:19 AM


All times are GMT +1. The time now is 02:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"