How would one create a single list of #'s (array) from multiple lists (arrays)?

**Rstrand** · 01-08-2013, 02:26 AM

Situation: I have data in multiple pages and I want to get the 95th percentile of a large array, combining data from each of the pages

The formula for Percentile requires the syntax as follows: PERCENTILE(ARRAY,k) where ARRAY is a continuous list, and K is a value between 0 and 1.
Here is the formula I tried:

Formula:

Please Login or Register  to view this content.

In the formula above, the italicized array contains multiple arrays within the list.

The problem: Excel cannot read past the first comma, thus, Excel is interpreting the multi-component array as:

PERCENTILE('sheet1'!AG4:AG119,(not k) blah blah blah blah….) and obviously returning an error.

This didn't work:

Formula:

Please Login or Register  to view this content.

Can anyone provide some light as to how to enter multiple arrays as a single array (where a comma separates the operators)?

**rossg** · 01-08-2013, 03:47 AM

try using an ampersand rather than commer....."&"
?

**Rstrand** · 01-08-2013, 06:32 PM

tried ampersand and returns #VALUE

Thanks for that.

It's not "SOLVED" But I'm not to try some error checks.

**Rstrand** · 01-08-2013, 06:37 PM

tried ampersand and returns #VALUE

Thanks for the suggestion, but this doesn't solve it.

I tried to reduce the number of terms, ie. go down to one ampersand, but still returns #VALUE.

Then I checked each term individually and all compute, so the ampersand doesn't work.

I'm going to try segregating each term and combine them with an ampersand..

I was expecting a very simple solution, but it doesn't appear that there is one, which is kinda wierd.

**Teethless mama** · 01-08-2013, 07:00 PM

Create a blank "Start" sheet, and a blank "End" sheet. The "Start" sheet is far left, and "End" sheet is far right. Just like sandwich. If you don't want to see start and end sheet just hide them.

=PERCENTILE(Start:End!AG4:AG200,0.95)

**Rstrand** · 01-08-2013, 07:24 PM

Or alternately, in this case I could replace Start with sheet1 and End with sheet4 ?

**Rstrand** · 01-08-2013, 07:26 PM

Thanks Teethless.

SOLVED!

Side note on this:

The data is required to be in the same position on all sheets, and, if you wanted to neglect any part of the data this formula would not work.

**Teethless mama** · 01-08-2013, 07:32 PM

You're Welcome!

How would one create a single list of #'s (array) from multiple lists (arrays)?

LinkBack

Thread Tools

Rate This Thread

Display

How would one create a single list of #'s (array) from multiple lists (arrays)?

Re: How would one create a single list of #'s (array) from multiple lists (arrays)?

Re: How would one create a single list of #'s (array) from multiple lists (arrays)?

Re: How would one create a single list of #'s (array) from multiple lists (arrays)?

Re: How would one create a single list of #'s (array) from multiple lists (arrays)?

Re: How would one create a single list of #'s (array) from multiple lists (arrays)?

Re: How would one create a single list of #'s (array) from multiple lists (arrays)?

Re: How would one create a single list of #'s (array) from multiple lists (arrays)?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions