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
853 Views

Similar Articles

[PageSpeed] 38

>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:

How do I do check boxes in cells?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel One of the only things I like better about iWork vs. Office is the ability for Numbers to *easily* format a cell with a check box. I'm looking for ANY way to add a checkbox to a cell so I can make a simple checklist. <br><br>I see a checkbox control in the Form palette but it's behavior is not what I'm looking for and it seems to be useless unless I write a macro or script to make it work. <br><br>Please tell me this is possible. <br><br>Thanks. > This messag...

how do i change the color of a cell auotmatically?
I am desiging a data shee and i want to changes the color of a cell based on the valuse of anthor cell. ex. if "cell m34" is equal to 0 then "cell p21" is white, if cell m34 less than 1548, then "cell P21" is yellow Can this be done? If so, please help me out. Hi! Select cell P21 Goto Format>Conditional Formatting In the dropdown select Formula is Enter this formula in the box: =AND(M34<>"",M34=0) Click the Format button and select the style to apply then click OK. Click the Add button In Condition 2 Formula is: =AND(M34>=1,M34<=154...

Is it possible to have text and currancy in one cell and ...
Is it possible to have text and currancy in one cell and be able to use the numbers in that cell to make an equation with another? The short answer .. No -- Rgds Max xl 97 --- Please respond in thread xdemechanik <at>yahoo<dot>com ---- "thegen" <thegen@discussions.microsoft.com> wrote in message news:1552FF10-9CAA-42AD-B401-B0D229D6A343@microsoft.com... > Is it possible to have text and currancy in one cell and be able to use the > numbers in that cell to make an equation with another? well maybe depending on how the "text & currency" was ar...

How to skip cells with text strings.
What should I add to the code below to cause it to skip a cell tha contains a text string in the cell above it? I need it to only work o numbers in the cell above it. If ActiveCell.Offset(rowoffset:=-1) <> "" Then ActiveCell = ActiveCell.Offset(rowoffset:=-1) + 1 Application.SendKeys ("{Enter}") End If Thanks so much. mikebur -- mikebur ----------------------------------------------------------------------- mikeburg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2458 View this thread: http://www.excelforum.com/showthread.php?thread...

Diffrent cell on diffrent date?
I�m going to TRY and explain this the best I can. For work when we do our cash out at the end of the night we use a exce sheet. This sheet is to add up our change, checks, credit cards etc etc. etc. When we're done we print it out and than type the figure into another excel sheet. I pretty much know how to combine the sheets so instead of typing the numbers into SS1 and than entering i into SS2 I can have the numbers going from SS1 to SS2 automatically But my question is that how do I set it up so when I type the info i SS1 that it will go to in to SS2 but to have the cells change by dat...

need drop-down arrow in cell
I want to create a spreadsheet for others to update by adding a row of data and filling in the required information described by column headings. Some columns will require a choice from a list of categories while others are freetext. For the category columns, is it possible to provide a combo box type list upon clicking on the appropriate cell that will display the categories and allow the user to choose one. Upon choosing the category, the value is entered in the cell. I do not want to draw a combo-box control in every cell of the sheet and I also do not want to create in input form...

what command shifts all cells in a column up or down?
Accidentally did something in an Excel worksheet to shift all cells in a particular column down. Do not know the command(s) or keystrokes to reverse what I did and shift all cells in the column up. select the inserted cell Edit>Delete when asked, click "Shift cells up" -- Kind regards, Niek Otten "marco" <marco@discussions.microsoft.com> wrote in message news:C0CAFF80-291E-4A62-9D52-980D94F74378@microsoft.com... > Accidentally did something in an Excel worksheet to shift all cells in a > particular column down. Do not know the command(s) or keystrokes...

Ignoring text in a cell with a formula
I have several cells which I want tobe able to have text and numbers in, but for a formula to ignore the text. The text is not the same in each cell. ...

Microsoft Excel
I have a particular group of cells within a sheet which I need to group together and sort. However I need to keep the rows of information locked together so when you sort the data it doesnt get all mixed up. Is there a way to do this? If so, how? If it is possible to do the above is it possible to have it to automatically sort the information as your adding it into cells? Is there a way that a "sort" function can be set automatically for a group of particular cells? For example; I have set 6 columns on a spread sheet "Column B - Column G" Say I have 150 rows of informa...

Defining a range as a subset of cells in another range
If I have the following range: Row/Col A B 1 2 10 2 2 2 3 3 8 4 4 5 5 3 10 6 2 3 7 2 5 8 1 5 9 2 6 10 3 9 Suppose I have a function that is called by MyFunction(B1:B12). Is there any way within the MyFunction routine to define a range of the numbers in column B for which column A = 2. If I were doing a SumProduct, the idea would by SumProduct(--(A1:A10=2),B1:B10). In this case, however, I just want a range that is the equivilent of (--(A1:A10=2),B1:B10). The new range would be include cells B1, B2, B6, B7 and B9. I was hoping to be able to do this without any k...

Excel Formula Guidance. Formula need to determine if cell is popul
Hi, Hopefully someone will be able to provide some guidance. I have been tasked with setting up some spreadsheets at work to application coming into my department and the number of days it takes for us to deal with them. To work this out is easy enough using formula =NETWORKDAYS(B4,E4,$A$119:$A$158) with the numbers involved with the $ referencing bank holidays listed in the spreadsheet and B4 received date and E4 being the completed date. I now need to include some extra lines as I need to be able to monitor written enquiries as well so the headings would read Application ...

Coping numbers to next blank cell on another worksheet
I am just learning Excel (need SIMPLE help) but any help would be greatly appreciated. My OS is XL Professional and I have MS Excel 2002 SP2. I need to copy calculated numbers (I got this much done on my own) in specific cells on worksheet # 1 to the next blank cells in existing columns of numbers used in calculations on other worksheets in the same workbook. I other words, I have a formula that calculates numbers and places the results in the same column and row each time it is used. Those calculated numbers need to be copied and used on different worksheets in the same workbook. While...

cell reference problem
I have a spreadsheet that is linked to many supporting spreadsheets and I send the master spreadsheet to the CEO and continue to have him receive the spreadsheet and the some of the cells that are linked have the ref# in the box and need to be updated, how can I avoid this from happening. I need a better way to have the information entered on the supporting spreadsheet automatically be entered into the master spreadsheet with all company statistics. Any ideas. The way is now works is by typing the =and then double clicking the cell with the information, there must be an easier met...

protecting cell depending on value of another cell
I tend to limit data entry on my worksheets by unchecking the "Lock" box (Format Cells -> Protection -> then uncheck lock box) and then protecting the sheet (Tools -> Protection -> Protect Sheet) with only "Select unlocked cells" checked. I am creating a worksheet where some of the cells require entry only if there is a certain value in a certain cell. For example: if J3="BLING" ... then Q13 is locked if J3="CLATTER" ... then Q13 is unlocked Is it possible to lock or unlock a cell automatically according to the value of one of the c...

Make subform visible when a field has a particular value
Hi, I have a field in my form that is a text field, but will only be entered as Y or N. If the field is Y, I want a subform to be displayed, if it's N the subform can stay hidden. I can't work out exactly how / what to have the subform bound to to make this work. thanks JJ Use the link master field and link child field to link the subform to the main form. Use the after update event of the textbox to show or hide the subform. Use the current event to show or hide the subform when opening an existing record for editing. Jeanette Cunningham MS Access MVP -- Melb...

Protecting single cells or a range
How can I protect a single cell or range so that the rest of the sheet is able to be accessed. I can only see options to protect the whole sheet or workbook. Thanks in advance. Good afternoon Ant Protecting a worksheet only protects the cells that are locked. In a new worksheet all cells are locked by default. Unlock the cells you want the users to be able to edit via ctrl + 1, Protection and uncheck the locked box. This can be done an many selected cells at once. Then apply protection with Tools > Protection > Protect Sheet. HTH DominicB -- dominicb ---------------------...

No Graph Visible
I am in the middle (60%) done rolling out Dell 430 or Dell 630 laptops to 2,500 users. Some, not all when they create a graph do not see anything:- Windows Vista, Office 2007 Create a Graph, F11 or Full screen the screen is all blue (the background colour) This seems to happen when users undock their laptops from a docking station not then connected. Any help appreciated. Roger Knowles MOS Master Office 97-2003 In at least one case I've heard of, this problem was fixed when a printer was installed. Apparently Excel needs the printer driver to help render the full page chart. ...

date range in one cell
I have a list of dates in one column. In one cell, i need to know the range. I tried getting the min value in one cell , then the max in another, then i combined the two using CONCATENATE however, that only returns the value (3901739023). Any suggestions to show the min (earliest date) and max (latest date) in the same cell? " 10/27/06 - 11/02/06 " "date dash date" Thanx. =TEXT(MIN(C:C),"mm/dd/yy")&"-"&TEXT( MAX(C:C),"mm/dd/yy") -- Don Guillett SalesAid Software dguillett1@austin.rr.com "J.W. Aldridge" <jeremy.w....

Manual Input of Cell Refs
I need to do a calc on sheet 1 which will use data from sheet 2. I want to point to the row on sheet 2 by manually inputting its number in sheet 1 at ,say, A1. Ex: In sheet 1 a3 I have the formula =Sum(sheet2!A?:G?) where the value of ? has been inserted manually in Sheet1 at A1 TIA Craig Hi try = =Sum(INDIRECT("'sheet2'!A" & A1 & ":G" & A1)) -- Regards Frank Kabel Frankfurt, Germany Craig wrote: > I need to do a calc on sheet 1 which will use data from sheet 2. > I want to point to the row on sheet 2 by manually inputting...

Pivot Table Page Field value from cell in another worksheet
I have looked through the other posts on the subject and the combination of my low skills in VB and the other solutions not exactly covering my situation means I still need to ask for help. Scenario: Worksheet a = "Scorecard" FieldName = "CustomerNumber" Worksheet b = "Products Resume" PivotTable = "PivotTable2" PivotTable Page Field = "Account Number" When CustomerNumber value on worksheet "Scorecard" is changed I need the Pivot Table Page field "Account Number" to use this value to filter the pivot t...

highlight range if cell contains desired data
If any cell in column D:D contains desired data in string format ( i.e. "Bill G" ), how would I highlight the data range in that respective row? Example: cell d4 contains the text "Bill G". Excel automatically colors the range 4a:4i in red. 1. Select columns A to I. 2. Go to Format > Conditional Formatting 3. Select "Formula Is" on the drop-down arrow, and put: =$D1="Bill G" 4. Press the Format radio button and format as desired. HTH Jason Atlanta, GA >-----Original Message----- >If any cell in column D:D contains desired data in ...

Use a Carriage Return when typing data into a text cell
What is the code for entering a carriage return in a text cell.? (not using a char map lookup) I tried "& char(10) &" and various combinations using alt, with no luck. What I want is to type Far[the_char_return_code]Farley[ENTER] resultng in Far Farley in the same cell. I know it exists, because a long time ago I found a spreadsheet that had it in the cell, and I copied the cell. I have been using that, but going to that worksheet and copying that character is a bit of a pain in the butt. There HAS to be a better way of doing it than that. TIA Far Farley The Profe...

How do I substitute text with a picture as a "comment" to a cell
I have a spreadsheet where in one of the columns I want to have the cells display a picture as I mouse over, akin to creating a comment for the cell. Is that feasible? Thank you, Arik http://www.contextures.com/xlcomments02.html#Picture Tells exactly how to do it. "Arik S" wrote: > I have a spreadsheet where in one of the columns I want to have the cells > display a picture as I mouse over, akin to creating a comment for the cell. > Is that feasible? > > Thank you, > Arik ...

create a formula by using text from other cells
I need to effectively use multiple If/Vlookup functions. i.e. if it's Oranges, Lookup price history in "oranges worksheet". But I have too many categories to search in so this is not possible. So I had another solution, but can't make it work... In this case I am using a VLOOKUP formula and want to use one cell to search on (a1) and another cell to tell it which spreadsheet to search in (Sheet2) i.e. =Vlookup(a1,'[WorkbookData.xlsx](CellA2 i.e.Worksheet Name),2,False) So is there a way to create a formula that uses text from another cell? INDIRECT is yo...

Return a cell reference as a result of an IF formula
I am using an IF logical. If the result is true, I would like to return the SUM of a range or cells. Or at the very least return cell reference. eg =IF(K3<=DATE(2010,1,31),AND(K3>=DATE(2010,1,1))) Since this is a true statement, I would like it to return the SUM of A1:A10 Thank you. =if(and(date(2010,1,1)<=k3,k3<=date(2010,1,31)),sum(a1:a10),"whateveryouwant") Because you're using the complete month, you have other choices, too: =if(text(k3,"yyyymm")="201001",sum(a1:a10),"not in Jan 2010") =if(and(month(k3)=1,year(k3)...