Conditional Format top/mid/bottom 33% of cells, but ignoring blank cells

Hello,
I am trying to conditionally format the top middle and bottom thirds
of a range of data. Problem is, that the range needs to be flexible as
sometimes there may be a maximum of 36 cells with data, but sometimes
there may be less (so there are blank cells in the range that need not
be counted). The methods I have tried always include the blank cells,
and so it is not equally formatting the thirds (as it includes the
blanks cells as part of the bottom data)....hope Im making sense! Here
are the 2 methods Ive tried so far:(using excel 2003)

Top 34%:
=IF(INT(COUNT($D$3:$D$38)*34%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*34%)),MAX( $D$3:$D$38))<=D3
Middle 33%
=IF(INT(COUNT($D$3:$D$38)*67%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*67%)),MAX( $D$3:$D$38))<=D3
Rest of the data (bottom 33%)
=IF(INT(COUNT($D$3:$D$38)*100%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*100%)),MAX( $D$3:$D$38))<=D3

and

Top 34%:
=D3<=PERCENTILE($D$3:$D$38,0.34)
Middle 33%
=D3<=PERCENTILE($D$3:$D$38,0.67)
Bottom 33%:
=D3<=PERCENTILE($D$3:$D$38,1)
0
fishyleg (1)
3/25/2012 8:54:13 AM
excel 39879 articles. 2 followers. Follow

0 Replies
1425 Views

Similar Articles

[PageSpeed] 23

Reply:

Similar Artilces:

how can I keep top-level windows in their relative z-order
A top level window will come to the top of the z-order (here I am assuming no always-on-top windows) when activated, say by clicking on it. But the owner of a top-level window will never be brought above its owned windows. Does anyone know of a simple way to prevent the top-level windows of an application from changing their z-order when activated? Why do I want to do this? I have an application that allows transparent Flash controls. As you know, transparent Flash control must be housed in a top-level window (Actually, they must be housed in a WS_EX_LAYERED window and a WS_EX_LAYERED...

Need result not formula in cells
Hi everyone! Using E02 on XP. Having fits over this, can't figure out what is happening. Have a spreadsheet and want all of the records in Column AG to 'read' the contract number contained in cell G1. First record is in row 4, so in AG4 I typed =G1. Worked fine. Did a Fill;Down, realized I wanted =G1 in each cell so I tried to key that into AG5 and all I get is the formula (=G1) rather than the contract number. I've had that same problem with other formulas. My column is set to text, and I've tried changing the cell formats but to no avail. It has GOT to be ...

Counting number of non-blank cells in a range
Hi, =COUNTA(RawData!CT11:CT1748) used to work for me in an old Excel 2007 file. Does not seem to work in a new file. Am I missing a setting? Or, is there any other simple way? Regards... What does: "Does not seem to work" mean? Incorrect result? Some kind of error? The cell displays the formula and not a number? Keyboard starts smoking? Monitor explodes? -- Biff Microsoft Excel MVP "Android" <anupamksharma@gmail.com> wrote in message news:7d92b935-4027-4de7-8c64-ef50360365f3@d42g2000prb.googlegroups.com... > Hi, > > =COUNTA(RawData!CT11:CT1748) ...

Dragging a Cell Changes Formula
I am creating a spreadsheet which will be protected with a table of cells unlocked for the user to fill in. There are formulas which reference the unlocked cells. Is there anyway to prevent these formulas from changing if the user drags (or cut-and-pastes) the unlocked cell (ie, if they type data in the wrong cell and then drag that data to the correct cell, any formula reference these two cells gets modified)? Thanks! --- Message posted from http://www.ExcelForum.com/ Janet, Make the formulas "absolute" =A1 will adjust when it's moved or dragged anywhere. =$A1 will "...

specify non-contiguous cell range as arguments to TTEST()
Hello, I have data from two groups interleaved in the same column. For the sake of simplicity, let's imagine group A: rows 1-3,7-9; group B: rows 4-6,10-12. I want to compare groups A and B with a ttest -witout having to displace values everywhere-. However, I can not specify ranges, otherwise the ttest gets too many arguments (the coma for specifying ranges is taken as the coma that separates the different arguments. I tried using named selections (shift+click on cells of group A, then name 'groupA' at the left of the function bar, similar for group B), but it sti...

In Exel 2000, stop the blank cells (with formulas) from printing.
Exel 2000. I have entered formulas into 398 rows of a spreadsheet . The fromulas are written so that the cell remain blank until a value is entered into the referenced cells. When printing, the cells with formulas, even though they are blank, print resulting in several blank pages. Other then "select print", is there a way to only print the cells that have values? I like to apply Data|filter|Autofilter. Then I filter to only show the non-blanks and then data|filter|showall after I print. or.... (saved from a previous post) (from a previous post) Select your worksheet (I&...

Access 2007 + Time Formats
I have a database I am starting to create. I need some guidance in how to best create a time format for a "sports watch". What I need to record is minutes:seconds:hundredths, however the times will need be manually inputted or imported. There will also be basic calculations performed on times e.g car a time for lap 1 is 1:22:30 and this is 00:01:27 outside best time. So store times and then be able to calculate differences in time and store them. There seems to be two basic ways to go 1) create an input mask and create a text field as mm:ss:uu, only I am unsure ho...

Combine two formulas in one cell
I have a spreadsheet with formulas that calculates percentages and rankings. My boss wants to combine two formulas into one. He wants to see the percentage and the rank of that cell on the same line. Is there a way to combine two different formulas into one? Thanks for the help. T.R. You might get a better answer if you post the current formulas, but this may get you started: ="The Percentage is: " & text(yourpctformula,"0.00%) & " Rank is: " & yourrankformula You can use the =text() function to format the number the way you like. "T....

conditional update of checkbox dependent on combo box in separate.
I want to alter a checkbox from FALSE to TRUE based on the selection given in a combo box. The combo box and checkbox are in different tables related by the primary key (study number). In the coding for the databse, there are currently SQL commands which insert dates into target fields afterupdate of a 'date of randomisation' in a separate table. One of them is coded something like the below: SQL = "INSERT INTO TableA(Date1) VALUES ('" & DateOfRandomisation.Value + 30 & "') " DoCmd.RunSQL SQL SQL = "UPDATE TableA SET Date1 = '&q...

Checkboxes don't work in merged cells
Version: 2004 Hi, <br><br>I've created a an application in Excel 2003 (PC) that contains a large number of checkboxes (from Forms toolbar). <br><br>When my clients on Macs use the application in Excell 2004 however, the checkboxes don't always function. Upon further inspection, it seems the checkboxes in merged cells do not function on the Mac. Has anyone run into this issue? Any advice on how to get these checkboxes functioning without a massive redesign of my Windows product? <br><br>Thanks, <br> Andy ...

change link properties in a cell
Hello, Our accounting software creates an excel report that contains a link to an image. (file://H:\xxxxx\xxxx\xxxxx.tif) for example. The problem is we want to replace the path to the image with something that will point to a folder on a dvd for example. We will not be able to control which drive letter the end user will use. My questions then; Is there a way to change the path in the link without interacting with each one? There are well over 1000 links in each spreadsheet. Is there a way to make the new path relative to the location of the spreadsheet file? Thanks so much, k Perha...

multiple font formatting to a single cell
Hello, I am trying to use superscript in a cell to just one number not the entire text in that cell. I can format it ok, and the formatting is there. However, once I hit the enter key, the formatting to the one number disappears. I'm new to xp's version of excel. I used to be able to do it in earlier versions of excel. Example: salary 2. I only want to add superscript to the number 2. Is there a way to do this in xp? Thanks ...

Why are lines appearing to the left and top of my jpeg images?
In Word and now in PowerPoint now, after I insert a .jpg image and save the document lines are appearing to the left and top of the image. Why is this happening and how do I stop it? It's not happening on every .jpg image only on some. Cropping will get rid of it but I have a logo that is already cropped as much as it can be and I can't crop it anymore! Any advice is appreciated. In article <570BA9EB-BFEF-4EA1-B698-B1EDDFE574AD@microsoft.com>, SFL Beach Girl wrote: > In Word and now in PowerPoint now, after I insert a .jpg image and save the > document line...

Scroll bars in Cell
Hi all, It seems like you can only size a cell so that it is 409.50 pixels. I have large ammount of text that I would like to put into a cell, and be able to scroll through that text. It would keep the size of the cell minimum, and allow the user to easily see what are the contents of the cell. Is this possible in Excel 2000? Is there a similar way to do this? Thanks for your help, Stephen Hi Stephen This is not possible for a cell (that is attach scrollbars to it) -- Regards Frank Kabel Frankfurt, Germany Stephen Closson wrote: > Hi all, > > It seems like you can only size a ...

delete rows where that contain an empty cell
I have 3 columns of data....columns 1 and 2 all have data...where column 3 contains an empty cell I would like to delete the row. Can someone help me please?? ....Lisa Try this Sub Delete_rows() i = 1 Do While Range("A" & i) <> "" If Range("C" & i) = "" Then Range("A" & i).Select Rows(i).Select Selection.Delete Shift:=xlUp Else i = i + 1 End If Loop End Sub This assumes that your data is in couln A,B,C and the start of the data is in A1 If your data doesn't start in A1 then change the i=...

Formatting and Styles throughout
I have a question for you about formatting and styles. I've used these in many programs and am well-versed using them in Word. I have now to do so in Publisher and am new to the program. The question is: how are styles most easily applied in a large document when things like headers and then body-text are grouped in style but not location. For example, a list of features and their operation. The headers and body-text are mixed throughout. Do I create a separate text file for each feature heading and then a separate file for each text body that is associated? If so, how do I keep them lin...

some text shows in merged cells but not all
I am inputting text to a spread sheet that has previously been formated by somebody else. The cells have been merged and set to wrap text. Only an amount of the text shows in the merged cells, even if I expand them. Double clicking on the cells shows all the text but as soon as I come out it reverts to show only part of the text. The whole text also shows in the formula bar. Please help Change formatting in the cell from 'text' to 'general'. -- -Kaman The IT dept people call before they call the real IT dept. "Jon" wrote: > I am inputti...

What format will excel "auto-parse"
Hi, I have a web server which I would like to output information in a format that can be copied&pasted into excel in such a way that the user does not have to follow up this step with the Text To Colomns feature. I'm outputing plain text and my MIME type is "text/plain". I'm delimiting my would-be columns with tabs but that doesn't seem to do the trick. Thanks! Bura Hi Bura, If you copy tab-delimited data & paste them into Excel, Excel will automatically separate the data into columns. Alternatively, if you separate the data with commas, and use 'csv&#...

conditional max
-- Brian Re: conditional max An example? Supposing C1:C5 contains: 1,2,3,45,23 If you array-enter* in say, D1: =MAX(IF(C1:C5<10,C1:C5)) *press CTRL+SHIFT+ENTER (instead of just ENTER) D1 will return 3, which is the max number of all numbers less than 10 within the range C1:C5 -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Brian Cornejo" <BrianCornejo@discussions.microsoft.com> wrote in message news:4B5378C0-E6F6-49CC-B877-08525073F0B0@microsoft.com... > > -- > Brian ...

Export query to Excel with formated heading
When export from query to Excel, I'd like to turn some columns into heading row on top of Excel sheets how to turn this: LastName FirstName SID Section Instructor Doe John 1234 0545 Su Doe Jane 2345 0545 Su into this? Class Roster Instructor: Su Section: 0545 LastName FirstName SID Doe John 1234 Doe Jane 2345 I think one way of achieving this would be to create a report in Access where you would group under instructor with the students in the detail section. From the report you can then use the analyze with Ex...

Hyperlink Text in Cell
I thought this would be pretty straight forward but I guess I was wrong. I want to create a hyperlink to a web site within a cell. But, I have a large amount of text in the cell and I don't want the whole cell hyperlinked - just the one word. Can this be done? Not directly. I've faked it by formatting the word(s) as a hyperlink and placing a borderless transparent textbox with the hyperlink over the words (set to move and size with cells). In article <2A99C38C-1F27-4FE9-B600-DDAFDA4FF5E3@microsoft.com>, "Jason" <Jason@discussions.microsoft.com> wrote...

Is there a formular or format to change 1 to one
Is there a formular or format to change a number "1" into the word "one". Hi Kevin, See: http://www.xldynamic.com/source/xld.xlFAQ0004.html --- Regards, Norman "Kevin" <Kevin@discussions.microsoft.com> wrote in message news:3C099B0B-82F6-44FB-AD72-B0F726256F96@microsoft.com... > Is there a formular or format to change a number "1" into the word "one". See http://www.xldynamic.com/source/xld.xlFAQ0004.html -- HTH RP (remove nothere from the email address if mailing direct) "Kevin" <Kevin@discussions.micro...

Conditional formatting #31
Hi, In a row of 6 numbers I want to highlight the 3 largest numbers. If the row contains 7 or 8 numbers, I want to highlight the 4 largest of those numbers. If the row contains 9 or 10 numbers, I want to highlight the 5 largest of those numbers. Any suggestions on how to make this work? Thanks in advance, Barney "Barney" <postonbl@yahoo.com> wrote in message news:4m8tf.82355$aS5.36606@bignews4.bellsouth.net... > Hi, > In a row of 6 numbers I want to highlight the 3 largest numbers. If the row > contains 7 or 8 numbers, I want to highlight the 4 largest of th...

Union versus multiple join conditions
Hello, I was working on a query that had multiple join conditions, and even though there was an index covering all of the columns used in the join (none of the columns were the primary key for either table), it was taking a long time (~20 seconds). The original query looked like: select b.* from a inner join b on a.num = b.num or (a.name = b.name and a.date = b.date) I rewrote it as: select b.* from a inner join b on a.num = b.num union select b.* from a inner join b on a.name = b.name and a.date = b.date and suddenly it's sub second. I don't sup...

Cell fill color in linked cells?
I am using a 2007 workbook as a Training schedule for a military command. I deal with various different military units and in order to quickly tell which is which they are color coded with specific fill colors. Normally the units will copy my spreadsheet and place their information at the bottom for their individual use. So to correct that, I liked my master schedule workbook (source) to a another workbook (destination) that they can use so that when I make changes, the top part of their schedule will be updated. Everything works perfect except the "cell fill color"...