Using PERCENTILE with *specific* cells in a column

I am trying to use the PERCENTILE function with specific cells in a column.

For example, lets say the column is:
    A
1  5
2  6
3  7
4  8


Using PERCENTILE on say A1:A3 is easy enough, but I want to do say A1 and A4.

I can achieve this by defining a named range such that it would be equal to 
A1 and A4, but would rather not use named ranges, as the above is a very 
simplistic example of what I want to do -- using named ranges would require a 
substantial amount of effort to achieve my end goal.

I also realize I could re-order the rows such that I swap A4 with A2, 
therefore could use percentile on A1:A2, but that will not work for my needs 
either.


I attempted to use the INDIRECT function in an array constant, but realized 
that you can't use a function in an array constant, so that rules that 
thought out.

Is there a way I can "create" the range to be used in an Excel formula (ala 
the RANGE function in VBA)?  The named range is doing this (as it works), but 
I can't figure out how to emulate it within my Excel workbook.

Thanks for any advice


0
zodiac711 (10)
3/11/2009 7:20:06 PM
excel 39879 articles. 2 followers. Follow

3 Replies
582 Views

Similar Articles

[PageSpeed] 54

Try =PERCENTILE((A1,A4),0.95)
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"zodiac711" <zodiac711@discussions.microsoft.com> wrote in message 
news:E38C3FDF-1775-42CB-BA50-56609144036A@microsoft.com...
>I am trying to use the PERCENTILE function with specific cells in a column.
>
> For example, lets say the column is:
>    A
> 1  5
> 2  6
> 3  7
> 4  8
>
>
> Using PERCENTILE on say A1:A3 is easy enough, but I want to do say A1 and 
> A4.
>
> I can achieve this by defining a named range such that it would be equal 
> to
> A1 and A4, but would rather not use named ranges, as the above is a very
> simplistic example of what I want to do -- using named ranges would 
> require a
> substantial amount of effort to achieve my end goal.
>
> I also realize I could re-order the rows such that I swap A4 with A2,
> therefore could use percentile on A1:A2, but that will not work for my 
> needs
> either.
>
>
> I attempted to use the INDIRECT function in an array constant, but 
> realized
> that you can't use a function in an array constant, so that rules that
> thought out.
>
> Is there a way I can "create" the range to be used in an Excel formula 
> (ala
> the RANGE function in VBA)?  The named range is doing this (as it works), 
> but
> I can't figure out how to emulate it within my Excel workbook.
>
> Thanks for any advice
>
> 


0
bliengme (657)
3/11/2009 9:04:01 PM
Bernard,

Thank-you!  I can't believe it was THAT simple...  I consider myself to be 
highly proficient with Excel, but could NOT figure that out...!  Argh!


Thank-you again -- that worked exactly as I needed it to.

"Bernard Liengme" wrote:

> Try =PERCENTILE((A1,A4),0.95)
> best wishes
> -- 
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
> 
> "zodiac711" <zodiac711@discussions.microsoft.com> wrote in message 
> news:E38C3FDF-1775-42CB-BA50-56609144036A@microsoft.com...
> >I am trying to use the PERCENTILE function with specific cells in a column.
> >
> > For example, lets say the column is:
> >    A
> > 1  5
> > 2  6
> > 3  7
> > 4  8
> >
> >
> > Using PERCENTILE on say A1:A3 is easy enough, but I want to do say A1 and 
> > A4.
> >
> > I can achieve this by defining a named range such that it would be equal 
> > to
> > A1 and A4, but would rather not use named ranges, as the above is a very
> > simplistic example of what I want to do -- using named ranges would 
> > require a
> > substantial amount of effort to achieve my end goal.
> >
> > I also realize I could re-order the rows such that I swap A4 with A2,
> > therefore could use percentile on A1:A2, but that will not work for my 
> > needs
> > either.
> >
> >
> > I attempted to use the INDIRECT function in an array constant, but 
> > realized
> > that you can't use a function in an array constant, so that rules that
> > thought out.
> >
> > Is there a way I can "create" the range to be used in an Excel formula 
> > (ala
> > the RANGE function in VBA)?  The named range is doing this (as it works), 
> > but
> > I can't figure out how to emulate it within my Excel workbook.
> >
> > Thanks for any advice
> >
> > 
> 
> 
> 
0
zodiac711 (10)
3/11/2009 9:30:19 PM
You are very welcome. Thanks for the feedback
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"zodiac711" <zodiac711@discussions.microsoft.com> wrote in message 
news:CB9D2D99-C65B-4770-B7B3-E7A660C1B9A3@microsoft.com...
> Bernard,
>
> Thank-you!  I can't believe it was THAT simple...  I consider myself to be
> highly proficient with Excel, but could NOT figure that out...!  Argh!
>
>
> Thank-you again -- that worked exactly as I needed it to.
>
> "Bernard Liengme" wrote:
>
>> Try =PERCENTILE((A1,A4),0.95)
>> best wishes
>> -- 
>> Bernard V Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>> remove caps from email
>>
>> "zodiac711" <zodiac711@discussions.microsoft.com> wrote in message
>> news:E38C3FDF-1775-42CB-BA50-56609144036A@microsoft.com...
>> >I am trying to use the PERCENTILE function with specific cells in a 
>> >column.
>> >
>> > For example, lets say the column is:
>> >    A
>> > 1  5
>> > 2  6
>> > 3  7
>> > 4  8
>> >
>> >
>> > Using PERCENTILE on say A1:A3 is easy enough, but I want to do say A1 
>> > and
>> > A4.
>> >
>> > I can achieve this by defining a named range such that it would be 
>> > equal
>> > to
>> > A1 and A4, but would rather not use named ranges, as the above is a 
>> > very
>> > simplistic example of what I want to do -- using named ranges would
>> > require a
>> > substantial amount of effort to achieve my end goal.
>> >
>> > I also realize I could re-order the rows such that I swap A4 with A2,
>> > therefore could use percentile on A1:A2, but that will not work for my
>> > needs
>> > either.
>> >
>> >
>> > I attempted to use the INDIRECT function in an array constant, but
>> > realized
>> > that you can't use a function in an array constant, so that rules that
>> > thought out.
>> >
>> > Is there a way I can "create" the range to be used in an Excel formula
>> > (ala
>> > the RANGE function in VBA)?  The named range is doing this (as it 
>> > works),
>> > but
>> > I can't figure out how to emulate it within my Excel workbook.
>> >
>> > Thanks for any advice
>> >
>> >
>>
>>
>> 


0
bliengme (657)
3/12/2009 1:09:08 AM
Reply:

Similar Artilces:

Right Click to Autofill cells in 2003
Hello All, In 97 to 2002 it was possible to right click when using Autofill to copy the cells down rather than continuing the sequence. in 2003 i just get a pretty red line (what purpose does this serve?). Does anyone know if I can get back the old functionality? Many thanks, Danny I have that functionality in Excel 2003 as well, maybe you have installed an add-in? In any case if you hold down ctrl while using the left click copy down it will also copy as opposed to fill a series Regards, peo sjoblom "DannyJ" wrote: > Hello All, > > In 97 to 2002 it was possi...

With Microsoft FAX Nearly One Minute Delay between Using Print
With Windows Messaging and Microsoft Fax under Win 98 SE, when I printed a document to Microsoft Fax, the fax wizard would start a few seconds after I said print. I installed Outlook 2000. With it, there seems to be a consistent nearly one minute delay between when I say print and the fax wizard starts. If Outlook is running, there is no delay. The osa9.exe program doesn't make any difference. Needless to say, this is an annoyance, having to go through two steps, start Outlook and then print, rather than simply printing. Does anyone know of a way to make Outlook 2000 work like Messag...

How do I randomly shuffle the cells in a column?
In Excel 2003, I have a column, say a1-a10, with certain entries. I need to create another column, b1-b10, with the same entries assigned randomly (shuffled) to the cells. Just one way .. Put in B1: =INDEX(A:A,RANK(C1,$C$1:$C$10)) Put in C1: =RAND() Select B1:C1, copy down to C10 B1:B10 returns a random shuffle of what's in A1:A10 Press F9 to re-shuffle -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Jason D" <Jason D@discussions.microsoft.com> wrote in message news:36244977-7EE5-436B-8617-59C15310B080@microsoft.com... >...

Delete Columns
In Excel, I need a macro that will delete a column based on a cell that will contain a name based on an input box. Thanks, Bernie See you OTHER post. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Bernie" <bml66@aol.com> wrote in message news:1172699736.687460.300590@h3g2000cwc.googlegroups.com... > In Excel, I need a macro that will delete a column based on a cell > that will contain a name based on an input box. > > Thanks, > Bernie > ...

How to change cell shading/fill based on value in another cell?
I have a sheet with two columns of experimental data. Let's say the data is in F10:F25 (column 1) & G10:G25 (column 2). There is a value in another cell (A1) that contains a 1 or a 2. If A1=1, I want to turn shading in column 1 agree and in column 2 off. If A1=2, I want to reverse that. If there a worksheet function that will do that? I was hoping for something like: =cellshading(range,color) I could then execute one of these two statements: If A1=1 then cellshading(F10:F25,"Green") cellshading(G10:G25,"None") Else cellshading(G10:G25,"Green&...

Icon not set using MFC AppWizard
I create an application called "Test" with the MFC AppWizard. I uncheck "document/view architecture" in the wizard dialog. After the wizard generates my source files, I immediately compile. Although it created Test.ico in my resources, it does not show up in the upper left corner of my main application window. Does anyone know why? If I go through the above steps and select "document/view architecture", then it works as expected. What is the difference? Did you associate test.ico with the resource ID IDR_MAINFRAME? Did you draw both the 32x32 and 16x16 ve...

How can I pre-filter a table that is used in a query in a live dat
How can I pre-filter a table that is used in a query in a live database? I have about 47 queries (SQL text in a memo field in a table) that all reference a few tables in the same database. I would like to limit the table’s data instead of re-writing all the queries to include further criteria. The queries are very complex and it is not desirable to re-write the queries and use parameters. This database is live on an ASP.NET web page and there may be multiple people viewing the reports which are using these queries. Since there are multiple users I can not simply copy the tables into ...

using "voice" in Win 7 w/ Win 2008 R2
I have a script that I run during logon and a different one at logoff. the drive mapping part of the script works just fine. But the voice part doesn't. Now, If I copy the script from the network and run it locally, it'll run the voice part. Any ideas? I've removed the drive mapping for security purposes. Dim WSHNetwork Set WSHNetwork = CreateObject("WScript.Network") 'Grab the user name UserString = WSHNetwork.UserName 'Speech add on (make your pc say hello to you) '-------------------------------------------- Dim HourNow, Gre...

add result column to pivot table
I have 2 columns in a pivot table - decription and amount. I need to calc a % of each value of the total. I don't know how to do that. Add another copy of the Amount field to the pivot table's data area. To display, the fields horizontally, drag the Data button, onto the cell that contains the word 'Total' -- there's a picture here: http://www.contextures.com/xlPivot02.html Right-click on the heading for the second Amount column Choose Field Settings Click the Options button Type a Name for the field, e.g. Percent Choose to 'Show Data as' % of column Cli...

Can I forward all messages received from a specific domain?
I'd like to set up a rule for an exchange user that forwards all mail they receive from a particular domain to another user's exchange mailbox. I tried setting up a rule where the "From" field was configured with a domain name only (From: specificdomain.com). I tested by having someone from that domain send an email to the user, but the rule did not fire; the message was never forwarded. Is there a valid syntax for creating a rule that should fire for all email from a particular domain? It's not practical in this case to set up multiple rules to fire on incom...

Using Excel 2000 as Data source for Word 2000 document
Problem using Mail Merge using Word 2000 & Excel 2000. After numerous problems - Word failed to connect to Data Souce file then it would & then it wouldn't and so on - reasons which are unknown! The 106 records in 19 fields from the Excel Data Source are only transferring the information for the first 104 records. I am looking to add further records but obviously need to overcome this problem. Hi Malcolm- Obvious, perhaps, but are you certain that the records are in consecutive rows & no vacant columns separating the fields of data? Does the data range contain any Merge...

VB.Net question on using the XML DOM (xmldocument)
I am about to go bonkers trying to figure this out -- So here goes Currently I am building an xml document and have no problem building the document with one namespace designation using the DOM. Here is a snip-it of what I have <vl:VNETList xmlns:vl="http://www.1234.com/VNET/List"> <Template> <ID>Template_FFF4887BC8B4E98F85256EE60061576A</ID> I can’t figure out how to add a secondary xmlns reference that will look like this. <vl:VNETList xmlns:vl="http://www.1234.com/VNET/List" xmlns="http://www.5678.com/VNET/eiwm"> <Templ...

How keep chart-datapoints displayed when column-width is set to zero
Hello there, my data basis for a chart has one column, that I don't want to be shown in the spreadsheet. However, when I set this column's width to zero, the correspondig data dissapear from the chart. How can I have both, a hidden column and its values shown in the chart at the same time ? Kind regards, H.G. Lamy H.G. - Select the chart, then select Options from the Tools menu, click on the Chart tab, and UNcheck the Plot Visible Cells Only checkbox. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ hglamy wrote: &...

increase column number limit?
Is there a way to increase the number of columns beyond ~260? Thanks /b No, sorry. Dan E "bill northlich" <billn@wildpackets.com> wrote in message news:eE3bpxpYDHA.212@TK2MSFTNGP12.phx.gbl... > Is there a way to increase the number of columns beyond ~260? Thanks > /b > 256 is the max. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL2K & XLXP ---------------------------------------------------------------------------- Attitude - A little thing that makes a BIG difference -----------...

Charting software: what does The Economist use?
Any clue of what software the people at The Economist uses to create charts? I=92m wondering whether there is some software out there better than MS Excel that would give better control without the onerous workarounds. You can do pretty well with Excel, and the "onerous" workarounds aren't too bad once you've become familiar with them. They are generally a bit more onerous in 2007. http://peltiertech.com/WordPress/2008/05/30/magazine-quality-chart-economist/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - ...

How to get SUMPRODUCT on filtered cells
I'm currently using the formula =SUMPRODUCT((F2:I1475>=1)*(F2:I1475<=9999)) This gives me the number of the full range of cells but I need this to update whenever I use a variety of filters. Any help is greatly appreciated Sam.D Say we have data in A1 thru B29 like: flia value 0 pass 2 pass 1 pass 2 pass 1 pass 2 pass 0 pass 2 pass 1 pass 2 pass 1 pass 0 pass 2 pass 0 pass 1 pass 1 fail 1 fail 0 fail 2 fail 1 fail 0 fail 1 fail 2 fail 1 fail 1 fail 1 fail 1 fail 2 fail and are appling autofilter to column a. We want to use sumpr...

Sum Multiple Worksheets, same named cell
I have multiple worksheets containing the same named cell (e.g. Month_Total). On a totals worksheet, I am not able to use =SUM('START,END'!Month_Total) because of the local/global naming. Is there a way around this? (Month_Total will not always be in the same cell on each worksheet). Thanks! It seems to me that you have two options. 1. Name each of your "Monthly Total"s slightly different. OR 2. Do not use the cell range naming. "uncreative" <uncreative@discussions.microsoft.com> wrote in message news:60A150C6-50BC-4876-A31F-1C...

How to group all data from 1 column into several columns (Redo)
I have 2 columns in my spreadsheet, Company Names and Event. The Event column values are Event 1, Event 2, Event 3, Event 4 and the company names repeat for each of the events that they attended. So sometimes I might have a company listed 4 times with each Event corresponding to it in the next column. How can I consolidate all of the company names so that there is 1 row for each company and all events are on the same row but in different columns marked with an x (the column labels will be Company name, Event 1, Event 2, Event 3, Event 4). This is what I have... Company Name...

Columns to Rows
I'm sure this is a simple problem but I'm trying to convert columns o data into rows of data. What's the easiest way -- Message posted from http://www.ExcelForum.com Hi try the following: - copy your column (CTRL+C) - goto 'Edit - Paste Special) and choose 'Transpose' as action Frank > I'm sure this is a simple problem but I'm trying to convert columns of > data into rows of data. What's the easiest way? > > > --- > Message posted from http://www.ExcelForum.com/ Just read Tom's post, paste special and then transpose -- Messag...

Signature option disappears when using word as e-mail editor
Hello, I've got a weird problem. When I try to use word as my e-mail editor, I lose the ability to add signature. As soon as I check the box to use word as my e-mail editor, the signatures buttons are greyed out. Any advice? Thanks! Jeff Jeff Brooks-Manas wrote: > Hello, > > I've got a weird problem. When I try to use word as my e-mail editor, > I lose the ability to add signature. As soon as I check the box to > use word as my e-mail editor, the signatures buttons are greyed out. > > Any advice? > > Thanks! > > Jeff The signature option within ...

Automatically open different workbooks depending on user account used.
There are two userrs on the same computer. When user 1 opens Excel, I'd like oen workbook to automatically open. When user2 opens Excel, I'd like a diffferent workbook to automatically open. Is this possible? (Each user logs into Windows usning their uuser account.) The computer is running windows XP pro and Excel 2007. Thanks, Something like this might work for you. This is untested. Post back if this doesn't work for you. Place the following macro in the ThisWorkbook module. You would need to change ThePath to your real path, the user account names for Joe and Bill...

Search photos without specific tag in Photo Gallery
Hello! Is it possible to exclude photos with specific tag from search results? For example I want to search for all photos that are tagged "animal" but want to exclude photos with tag "cat". I've tried search queries like "animal -cat" but that does not seem to work. Any ideas? P.S. I'm using Vista SP2 Home Premium Photo Gallery -- Veld ...

Overlay 4 years of data as a line on 4 years of columns for several x category labels
Dear Charting aficianados, The tutorial at http://peltiertech.com/Excel/Charts/ClusterColAndLine.html is the closest example I can find to a charting conundrum I am facing, though what I am hoping to achieve is still a little different. I've made a mock up of what I'm hoping to achieve - though I have had to use drawing objects for the line series, to overlay on the columns - I'm sure there is a way to get excel to do this - do you think there is? (sheet named mock-up) A workbook with mocuk-up and my other charting attmempts can be found: http://www.savefile.com/files/2690840 T...

Use Excel to update Outlook address book
hi all... Is there a way to update outlook address book using excel? Is there a macro available for it? Thank you for your answers Mike ...

Algebra within a cell
How do you set-up a formula in a cell that multiplies a constant times the number you insert? Ex. the constant is .315 remains present at all times only the number you insert changes - =.315*(x) You can't unless you use an event macro, if you need a formula you have to use another cell as help =0.315*A2 where A2 holds x you can also put 0.315 in a cell, copy it, select the cel with x and then paste special and select multiply. But to get this instantly you have to use something like Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("A2"), Target)...