visible cell only

I'd like to use the PERCENTILE function in a list that has been autofiltered 
and get the results based only on the visible cells.

I've used SUBTOTAL in order to get count, average, min and max.  But I need 
to get the .25 and .75 percentile figures for the filtered data (visible 
cells only).

I've scoured these forums.  I've scoured the web.  I've found some vba code 
that was supposed to select only visible cells but it doesn't work for me.  I 
posted last week in the programming section of these forums (and again this 
morning) but got no reply.  I figured I'd try here.  I'm at my wits end.  
Please help me!

Many thanks in advance!!

Brad
0
Utf
4/12/2010 5:16:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
870 Views

Similar Articles

[PageSpeed] 27

>a list that has been autofiltered

Let's assume this is your unfiltered data:

76
45
64
85

Show me the formula you'd use to get the result you want and tell me what 
that result is.

Now, let's assume the filter is applied and this is your visible data:

76
45

What result would you expect?

-- 
Biff
Microsoft Excel MVP


"Brad Autry" <BradAutry@discussions.microsoft.com> wrote in message 
news:FC1BB104-DB45-4BF7-88CF-23DF4F59106A@microsoft.com...
> I'd like to use the PERCENTILE function in a list that has been 
> autofiltered
> and get the results based only on the visible cells.
>
> I've used SUBTOTAL in order to get count, average, min and max.  But I 
> need
> to get the .25 and .75 percentile figures for the filtered data (visible
> cells only).
>
> I've scoured these forums.  I've scoured the web.  I've found some vba 
> code
> that was supposed to select only visible cells but it doesn't work for me. 
> I
> posted last week in the programming section of these forums (and again 
> this
> morning) but got no reply.  I figured I'd try here.  I'm at my wits end.
> Please help me!
>
> Many thanks in advance!!
>
> Brad 


0
T
4/13/2010 4:36:58 AM
Reply:

Similar Artilces:

cell buffer and lining dollar amounts up over the
Is there a way to set text in from the sides of a cell? I've tried the left indent but I want a right indent. Sort of a cell padding. What I also want is to format a column so that dollar amounts to line up on the decimal. Is there a way to get dollar amounts to line up vertically on the decimal points? Thank you, John The basic number formatting codes are in Help (search for number format). In this case: 0.?? will line your numbers up with two digits to the right of the decimal. You can add space using the _ character. It leaves enough space for whatever follows it, so ...

Concatenate Cells
I am having trouble with a formula to concatenate the descriptions into 1 cell for each S/C #, some have 2, 3 of 4 cells. Material No. Full Description 904120016 VALVE,SOLENOID, 240V 50HZ, HERION, P/N 9301800-0201, FOR GAS SAFETY SHUT OFF AND VENT BALL VALVES, ON ALUMINA KILNS 907010005 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER, BLACK, PVC / V75, .6 / 1KV, 100M ROLL 907010006 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER, RED, PVC / V75, .6 / 1KV, 100M ROLL Can you elaborate more about it? You mean cells or rows? -- Greatly appreciated Eva "peterh" wrot...

Count cells in range that restarts at intervals
I have a table like this: Row Name Y/N? No. of Y 1 Adam Y 1 2 Adam Y 2 3 Adam N 2 4 Adam N 2 1 Brian Y 1 2 Brian N 1 3 Brian N 1 4 Brian Y 2 Is there a formula I can put for "No. of Y" that counts the no. of times "Y" appears for Adam from the first "Adam" row up to the current row? The counting should restart for Brian and not accumulate from Adam's total. Note that the starting point for each person is when "Row" = 1. Can a pivot table help? The -- is a double unary minus which forces the Sumproduct function to Convert True and False answ...

cross references to cells in a worksheet
I use cross references (ref field) in my Word form templates to pull table cell entries to other locations in the form or other pages. I can't find anything in excel help pointing me in the right direction for using cross references in an excel form that I am setting up. Is there a similar method? I want to identify specific cells in sheet 1 and have sheet 2 or 3 or 4 (however many additional sheets) pick up and reflect the entries made in the sheet 1 cells...... assistance is really appreciated Lenny Use a Named Range worksheet menu Insert - Name - Define -- joel ---------...

hiding cells #3
I wrote a very simple program showing my fuel oil consumption. It seems to work ok. my problem is hiding cells that have not in use yet. Is it possible to post my file? I know someone out there will laugh and do it very simply. thanks for any help. Each day I enter the date, the amount used and It shows how much is remaing in the 'tank' and how much i used each day. my problem, the sheet shows negative number where I haven't entered any data yet.. franbarb -- franbarb ------------------------------------------------------------------------ franbarb's Profile: http://www...

Show leading 0s for binary formatted cell?
How does one format a cell to display the leading zeros of binary data (or of data of any format other than decimal, for that matter)? In my case the binary data is created with "dec2bin" function. The decimal equivalent of the data is in the range of [0,15]. I want to display all 4 bits, including any leading zeros. For decimal data I can display leading 0s by: format cells->number->category- >custom and creating custom format "0000" which causes 4 decimal characters to be displayed including any leading zeros. When I apply the same format to binary data I...

How do I remove the percentage % from a cell and keep the number .
I have already typed 3 workbooks with the cells formatted as a percentage and now I need the cell displayed as a number but not calculated as a percentage, in other words going from display 3.5% to 3.5 ...

Format cell to accept only specific character-reject others
I am trying to format a cell so that it will only accept the characters H or F and reject all others. Select the cell, then select "Validation" from the "Data" menu. Select "Custom", then enter the following formula: =or(A1="H",A1="F") Replace A1 with whatever cell you are using. HTH, Elkar "aetzkin" wrote: > I am trying to format a cell so that it will only accept the characters H or > F and reject all others. Try this: Select the impacted cells Data>Data Validation Allow: List Source: F,H Click [OK] Does that h...

Possible to use only minutes and seconds in cell time format?
I'm trying to figure out a way to format cells so that I only have to input minutes and seconds only, rather than having to type in the hour as well. Is there a way that Excel will do this automaticaly, or is there a way to format without the hour being included? -- Thanks, Garehead If you enter a decimal point after the seconds, XL will interpret the input correctly. Alternatively, you might look at http://cpearson.com/excel/DateTimeEntry.htm In article <AE698335-6AC4-4DF6-A9CC-E5B96BF6D8B0@microsoft.com>, "Garehead" <Garehead@discussions.microsoft.com&g...

goto special blanks does not return any blank cells
I have a sheet that has a lot of blank cells in column A and data in colum B. I want to automatically fill in theblanks in A with the information from the cell above, so I can use the data in B in relation to A. Excel 2002 Try Debra Dalgleish's nice coverage on this at her: http://www.contextures.com/xlDataEntry02.html where she lists 2 methods to do so (one manual, the other programmatically*) *Sub FillColBlanks by Dave Peterson -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "Lobo" <Lobo@discussions.microsoft.com> wrote i...

Copy cell with Row Insert
This is a multi-part message in MIME format. ------=_NextPart_000_001C_01C78740.050CA540 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Here's the scenario. I have a spreadsheet with formulas in each row. The = formulas are relative only to the row they are in. I also protect the = workbook so cells with formulas are protected, columns can not be added = and hidden stuff can not be un-hidden. The Basic template for this = workbook has about 20 rows. The jobs this will be used on will require a = varying number of rows, up to several ...

can't change cell format
Hi, I am using Excel 2002 on my PC (Vista is the OS). There are a number of cells with the green tick in the upper left hand corner. I believe these are numbers that are formatted as text. I want them to be formatted as numbers but highliting them and using either the right click method or taking the options from the top does not change the format. These are numbers that have been typed directly into the cell and transferred from other worksheets. How do I get Excel to accept the change to a number format? TIA -- Larry Smith To change the format, you can select...

I want the results of a formula to show in cell, NOT THE FORMULA!
I have the new excel program and I can't figure out how to get the results of a formula to show in the cell instead of the formula itself. The old program displayed the results! That is what I want, how do I do that? ie....=SUMPRODUCT(+D3+D4+D5+D7+D8) should be xyz dollars, not .....=SUMPRODUCT(+D3+D4+D5+D7+D8). ARRRGGHHHHH I have had sinmilar issues - but forst make sure that in Tools -> Options -> View Tab - you havent got the "Formulas" check box ticked somehow. However if this is not the case, then on occaision I have had this - I got around it by copying ...

Run-Time Error on Cell Range Select
I get a run-time error 1004 on the .Select line. Can someone suggest a fix? Sub FindDate() Dim rn As Long, offset As Long Dim stdate As Date, actdate As Date actdate = Range("Z2").Value rn = IsoWeekNumber(actdate) If rn / 2 <> Int(rn / 2) Then rn = rn – 1 stdate = Range("A" & rn + 6).Value offset = actdate – stdate Range(Cells(rn + 6, 3 + offset), Cells(rn + 7, 3 + offset)).Select End Sub Phil- My best guess is your use of the offset statement, but here goes a longer response just in case that isn't it. What are your variable values...

name of tab into a cell
Hi In excel I want to put the name of the tab into the cell onto the shee (eg Sheet 2) Can someone let me know a formula to do this Than -- Andy B ----------------------------------------------------------------------- Andy B.'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1476 View this thread: http://www.excelforum.com/showthread.php?threadid=27557 Andy, try this, will return sheet name: =MID(CELL("filename",A2),FIND("]",CELL("filename",A2))+1,255) file must be saved first -- Paul B Always backup your data before trying so...

Audit cell reference on another sheet
Would it be possible to audit a reference to a cell in another sheet with the Tools Audit and get the exact cell location ie: Sheet2!C23. scooper Try double-clicking on the dotted line which points to a tiny ws icon. This will surface the "Go To" dialog which shows you the cell location. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "scooper" <robertc47@optusnet.com.au> wrote in message news:46c19b6d$0$6277$afc38c87@news.optusnet.com.au... > Would it be possible to audit a reference to a cell in another sheet with > the Tools Aud...

2 Data Validations in a Cell
Is it possible to put 2 data Validations in a Cell? I have posted previously on not allowing input in say, C5 unless there is a value in A5 (i.e. A5=<>""), but if I also want whatever is input in C5 to not exceed 20 characters, how would I do that in the same Validation Thanks Hi! Try this: =AND(A5<>"",LEN(C5)<=20) Biff "John" <john@yahoooo.co> wrote in message news:HGG5f.17395$R5.1477@news.indigo.ie... > Is it possible to put 2 data Validations in a Cell? > > I have posted previously on not allowing input in say, C5 unles...

format cells
cells BP36-38 won't change when I try to format them with a white or "n color" pattern. Pls. see attached file Attachment filename: san francisco county training sched-updated.xl Download attachment: http://www.excelforum.com/attachment.php?postid=59412 -- Message posted from http://www.ExcelForum.com Hi,' They're not formatted in the 'regular' sense, but conditionally. Go to menu--Format--Conditional format.. and delete the conditions. jeff >-----Original Message----- >cells BP36-38 won't change when I try to format them with a white or "n...

Avoiding #n/a cells
I have a few lookup formulas in range K18:K173 that are returning #n/a results. Those will disappear soon when data is entered in the data source. I'm trying to run summary totals, but the formula =SUMIF($K$18:K$173,B181,$J$18:$J$173) returns #n/a. How do I change this so that it ignores #n/a values in the range? Thanks. Hi Ferris you should change your lookup formulas in K18:K173. e.g. =IF(ISNA(lookup_formula),"",lookup_formula) HTH Frank Ferris wrote: > I have a few lookup formulas in range K18:K173 that are returning #n/a > results. Those will disappear soon whe...

How to navigate within a formula without changing cell references?
I have the following function in G20: =IF(C20>0,F20,"") I need to replace the second parameter (F20), with another IF statement. =IF(C20>0,IF(F20<>"",F20,"Error"),"") I go to G20 and press F2 to edit the formula. I move the cursor to just before the "F20" using the arrow keys and start typing in the new IF statement. When I have it entered, I need to move the cursor over the old F20 to finish the new IF statement, but Excel takes the arrow keys to mean I want to reference an adjacent cell. How can I get Excel to interpre...

combining cells #4
how do i combine two cells in two rows into one cell without losing data for example A1 information here needs A2 to be combined with information here using a helper cell =A1 & A2 Copy and Edit/Paste Special/Values back into a1 Via macro, one way: http://www.mcgimpsey.com/excel/mergedata.html In article <E8557060-AE72-4C4E-9523-1F3C897B7289@microsoft.com>, "lyneday" <lyneday@discussions.microsoft.com> wrote: > how do i combine two cells in two rows into one cell without losing data > > for example > > A1 information here needs &...

Charts within a cell
Can a cell be used a chart ? And show a simple range as a chart within a cell using a line or bars and Only using values given? a cell (bars) a cell (lines) ::::::::::::::::::: ::::::::::::::: : - : : - : : - : : - - : : - - - : : - - - : : - - - - : :- - - : : - - - - - : : - - : ::::::::::::::::::: ::::::::::::::: using range : ek7 is 4.2 em7 is 5.3 eo7 is 6.5 et7 is 3 ev7 is 5.4 Yes, hold...

Changing the order of a name in various cells
I have a column with over 400 rows of names that most contain names reading last name first then a comma then a persons first name. Such as Sullivan, Mike. How can I change the order to read first name then last name without the comma? Such as Mike Sullivan. Thank you for looking. -- Cheers, John Cannon Hi With entry in A1: =RIGHT(A1,LEN(A1)-FIND(",",A1)-1) & " " & LEFT(A1,FIND(",",A1)-1) -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "happydude" <happydude@discussions.microsoft.com> wrote in message news:9...

how do i prevent a row from moving, like subheader that is visibl
In Excel, how do I prevent a row from moving, like a subheader that I want to stay always visible at the top of my document? If you want row 1 to stay fixed, select row 2 & use Windows/ Freeze Panes. If you want to freeze both rows and columns, if you select cell C3, for example, before selecting Windows/ Freeze Panes, then it will freeze rows 1 and 2 and columns A and B. In other words it will freeze rows above and columns to the left of the selected cell. -- David Biddulph "Binmaru" <Binmaru@discussions.microsoft.com> wrote in message news:CCEF434B-24B4-429D-870...

Jump over 10 cells in a row
Hi, If A1=1, the next 10 cells fill with 88. How can I jump to the 11th cell? I tried highlighting the 10, Ctrl 1, unlocking & protecting, but that takes me down to the next row. Ideas? Thank you. Don't quite understand exactly where you're trying to go. Is the cell you want to "jump" to empty? If so, this will get you right *next* to it. Try selecting A1. Hover the cursor over *that* border of the selection in the direction that you wish to "jump", until the cursor changes to an arrow. Double click, and you'll jumpt to the last cell of contiguous data...