#### How-to apply conditional format

```I have a list of attendance at my schools' chess club. I'd like to
highlight those that have been absent for the past 3 meetings, as they
are now eligible to be replaced by kids on the waiting list.

Any idea how I can write a conditional format to check ideally the row
for check marks and highlight  the Name cell if three consecutive
cells are not checked or alternatively (2nd prize) if the total of
attendances are 3 less than the maximum number of attendances.

Also fine if I can get the bottom 15% of attendance and highlight
their names.

I had a conditional formula on the Name cells that had an =if()
statement on the cell counting the number of attendances, but that was
not the same as the bottom 15%. So if I can just figure out the
formula to put in the conditional format that would evaluate to True
if the attendance count is in the bottom 15%.

Thanks.
```
 0
MPBrede (29)
10/30/2009 9:07:32 PM
excel 39879 articles. 2 followers.

1 Replies
501 Views

Similar Articles

[PageSpeed] 20

```I think you need to describe how your data is arranged - what columns/
rows you use and how it is laid out. We can't "see" your worksheet.

Pete

On Oct 30, 9:07=A0pm, MikeB <mpbr...@gmail.com> wrote:
> I have a list of attendance at my schools' chess club. I'd like to
> highlight those that have been absent for the past 3 meetings, as they
> are now eligible to be replaced by kids on the waiting list.
>
> Any idea how I can write a conditional format to check ideally the row
> for check marks and highlight =A0the Name cell if three consecutive
> cells are not checked or alternatively (2nd prize) if the total of
> attendances are 3 less than the maximum number of attendances.
>
> Also fine if I can get the bottom 15% of attendance and highlight
> their names.
>
> I had a conditional formula on the Name cells that had an =3Dif()
> statement on the cell counting the number of attendances, but that was
> not the same as the bottom 15%. So if I can just figure out the
> formula to put in the conditional format that would evaluate to True
> if the attendance count is in the bottom 15%.
>
> Thanks.

```
 0
pashurst (2576)
10/30/2009 10:20:45 PM

Similar Artilces:

pivot table formatting #3
Using 2003 (11.5612.8107) This is my first real work with a pivot table. In 10 minutes I was able to create a beautiful table with exactly the desired look. However if there is any slight change, I have to rebuild things from scratch or at least reformat it. For example in the left most (Row field) i use Request Type. If I change the Request Types being displayed, the format of the table comes undone. I have read an old post and selected Pivot Table, Table options, Preserve Formatting. I've also clicked on Select, Enable selection. Yet if I add or remove one of the items to ...

Conditional Multiple Time Ranges
I have pivot tables that pull from a Data Warehouse that regenerates during 6 time ranges each day. I want to put a message on each pivot table that displays only during these time ranges. I have succeeded in putting a running clock time on the sheet, but can't figure out how to display my text for these 6 ranges. For example, between 8:00 AM and 8:15 AM and 6 other ranges thereafter I want to display text in a cell on the sheet that provides a message to the user. How do I specify these ranges and how do I conditionally show the text for 6 ranges? Thanks for your he...

Conditional Format Dates
How do I conditionally format dates that are more than one year old? -- -Andy On Thu, 17 Jan 2008 08:12:06 -0800, Andy12 wrote: > How do I conditionally format dates that are more than one year old? In form design view, select the date control. Click on Format + Conditional Formatting. Set Condition1 to Expression Is In the next dialog box, write: [ControlName]< DateAdd("yyyy",-1,Date()) Change [ControlName] to whatever the actual name of your control is. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail On Thu, 17 Jan 2008 08:12:06 -0800,...

How do I apply a combo box to multiple cells in Excel so that it .
I would like to apply a combo drop down box in Excel. However I only want the box to appear when the cursor is over the cell can anyone offer me some help on this matter? Thank you, Dwain Hi Dwain, Perhaps you are looking for Data Validation, available on the Data menu. See Debra Dalgleish's tutorial at: http://www.contextures.com/xlDataVal01.html --- Regards, Norman "DB" <DB@discussions.microsoft.com> wrote in message news:6939ADC2-CF1C-46AA-B163-BEE27E23356A@microsoft.com... >I would like to apply a combo drop down box in Excel. However I only w...

Apply view to all folders
Greetings, A user has numerous folders created under her Inbox in Outlook 2000. She woule like to create a custom view and then apply it to all folders automatically. I can create a custom view easily, but can't find a way to apply it to all folders other than on a one-by-one basis. Is there a way to apply the view to all folders at one time? Thanks much, Justin You can't apply it to all at once - unless you customize the Messages view. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Gu...

view applied?
What does the following WLM beta message mean? View applied not connected to imap4.xxxxxx.co.uk I had already clicked on this account and a couple of emails were downloaded. ...

Printing Formatted Cells
I have conditional format on a cell to format with say Fill Red ... It displays OK on the screen but doesn't appear when printed? I'm sure it'll be something obvious! Is your print setting for color, or B&W? Long shot, but I'm not sure of any other reason why what-you-see is not what-you-get. -- Best Regards, Luke M "Charlie" <Charlie@discussions.microsoft.com> wrote in message news:C94E101A-9579-4FDB-8AEF-91D59E1EC3A4@microsoft.com... >I have conditional format on a cell to format with say Fill Red ... It > displays OK on the sc...

format a cell as running time
Hello All, I have a simple question I cannot figure out. I would like to keep a spreadsheet of my workouts(running) . I have columns "Total Time" and Split Time(s)" (multiple of these) How would I format a cell as running time? i.e. 22.20.93 and say one of the splits is 2.28.03 (two minutes etc...) Thanks for any suggestions, Homer One way: Format/Cell/Number/Custom mm:ss.00 then enter 2:28.03 In article <94D844C9-A8C9-42FC-91DF-BCDD252ACC6E@microsoft.com>, "Homer" <Homer@discussions.microsoft.com> wrote: > Hello All, > I hav...

Conditional formatting
I've followed the correspondence on conditional formatting on charts with interest but is it possible to conditionally format a line chart i.e. green above a certain value, red below that value? -- Brisbane Rob ------------------------------------------------------------------------ Brisbane Rob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25096 View this thread: http://www.excelforum.com/showthread.php?threadid=529599 Hi, Yes it's possible. http://www.andypope.info/charts/conditionalline.htm Cheers Andy Brisbane Rob wrote: > I've follow...

M2005 Cannot Apply Epayment To Invoice
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C4AD73.81F30C10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I create invoices for payments from customers. I have those = automatically charged to their bank accounts and credited to my bank = account. When the payments hit my bank account and are downloaded into = Money, I go into the transaction and enter "Payment For Invoice" and = then try to go to the apply payment to an invoice screen but a nice = little pop up comes up in Money 2005 that says &qu...

Conditional Formatting by Date
Howdy All, I have a budget workbook which contains a worksheet for every month and I need to format certain cells to show a value from a specific date forward. Example: Car payment needs to be applied on the 15th and remain there when the month rolls over. So on the fifteenth of May, I need cell C5 to display \$350.00, but I don't want May's C5 to reset back to 0 when the date rollovers over to June 1st. I hope I am clear on this. Thanks for you time and attention, Brian hi! in A1: =TODAY() and in C5: =(IF(AND(MONTH(A1)=5,DAY(A1)=15),TEXT(350,"\$000"),"&q...

I've applied a Fill and now can't see the gridlines....
Does anyone know how to show the Gridlines through a fill? I've searched and searched but can't find the answer, yet the silly thing is I know it can be done as I have done it before (by accident) Any help mostgratefully received! bestregards, Tim Apply borders... In article <62E1EA83-287A-4FE3-BEC6-7C0155EA1850@microsoft.com>, "FizzyBunghole" <FizzyBunghole@discussions.microsoft.com> wrote: > Does anyone know how to show the Gridlines through a fill? I've searched and > searched but can't find the answer, yet the silly thing is I know it c...

today() conditional formatting
i have conditional formatting set for projects that are still pending so that formula is =today( formatted font is red, bol once the project is complete, the date is manually entered into the cell (changing it from the formula =today() to an actual date (i.e.: 4/19/04)) the problem is that the conditional formatting at that point still reads the date in that cell as today's date (even though it's no longer a formula), and continues to show as red, bold font... which indicates that the project is still pending is there a way to get around that hope that makes sense tia jill You...

Format Painter shortcut?
Is there a built-in keyboard shortcut for the Format Painter? If not, is there a way to to assign to some key combination? I know I can assign a key combination to a macro, but I would prefer not to write a macro just to get a keyboard shortcut for a standard function. Thanks -- Running Excel 2000 SP-3 on Windows 2000 i don't know of any but you could copy the cell with the correct paste special>formats and then use F4 to repeat the action in each cell you want to format "LurfysMa" wrote: > Is there a built-in keyboard shortcut for the Format Painter? > > If...

Cross Apply problem
How do I get the desired result for the following: SOURCE TABLE: USSoilLyr FIELDS: TAXOR PARTSIZE PCT_R Alfisols ashy 60 Alfisols ashy 57 Alfisols ashy 38 Alfisols clayey 85 Alfisols clayey 79 Andisols medial 50 Andisols medial 36 DESIRED RESULT (TARGET TABLE): T_Order FIELDS: TAXOR PARTSIZE PCT_R Alfisols ashy AVG(60+57+38) Alfisols clayey Avg(85+79) Andisols medial Avg(50+36) My T-SQL Code, as follows, returns wrong calculations when I manually check the results. UPDATE T_Order SET ...

Install V10 HR after applying service pack 3
I am currently on V10, SP3 and now have purchased the HR module. I am not sure of the steps I need to take to load the HR module (we have the V10 DVD that is not sp3) Are these my steps? 1. Load the HR module from V10 DVD on SQL Server install (I was told previously to use the Add/Remove programs option and change but it still asks for the DVD) 2. Do I need to reload SP3 on server install? 3. Then load the HR module (from V10 dvd) on additional workstations? If I have to re run the SP3 I am concerned it is going to take hours to complete like original instal of SP3 If anyone has a...

Apply Multicurrency Invoices in Bank Management
I oppened a ticket to know why I cant apply Multicurrency Invoices to payment transactions in Bank Managment as it is available in Payable Management. I've been suggested to post a suggestion for that. Simply, while doing a payment in Bank Management. If the Chequebook Currency is diffrenet than the invoices currencies that you are paying againist, then you can't apply these invoices. Thus, you cant see them in the remittance advice. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestio...

unposted applied credits
how can list all unposted applied credits in AR. Thanks, Scott If you have SmartList Builder, you could use it to create a SmartList based on the Cash receipts work file. "Scott Rusoff" wrote: > how can list all unposted applied credits in AR. > > Thanks, > Scott > Create a report off RM10201 (RM Cash Receipts Work File). Add a restriction where Current Transaction Amount <> Original Transaction Amount. "Scott Rusoff" wrote: > how can list all unposted applied credits in AR. > > Thanks, > Scott > Which smartlist contains t...

Strip all formatting in cut and paste!
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel In Word's preferences, I've always deselected, &quot;Include formatted text in Clipboard,&quot; yet the formatting always seems to show up when I cut and paste, which I hate. <br><br>I cut and paste many, many times a day and I NEVER want the formatting carried into Word. <br><br>I get alarmed when the formatting comes over, so I avoid the dropdown, post-paste &quot;Keep Text Only&quot; option. I also know there's some wacky keystroke combination to strip for...

Payables issue with Applied Payments and Void Check
Hi, We had a user a number of months ago apply a number of Credit Memos from a vendor to a large invoice while creating a check. Apparently the check was then void, without a normal check number leaving us with "REMIT0000000000xx" as the reference to it. We now have a situation where the large invoice and all of the credits are still showing in the system as open. The individual credit memos do not show as having amounts applied, but when we try to apply them we're told that the amount is already applied. Everything seems to be stuck in limbo, with no way to post to resol...

Signature formatting problem
I have a HTML file that I include in my WLM signature file. When I open the file in IE8, everything looks perfect. But, when I make that file my signature file, all the text lines are double spaced. My contact data normally measures about 1" in height (when viewed with IE) but when it gets plugged into my Signature file, it's 2" and double spaced. Any help appreciated, Tom Are you using <p> tags? Can you post the code? x out private information. -- Ron Sommer MS MVP- Windows Live Mail "Tom Brown" <Burner2_DELETE_NOSPAM@att.net> ...

conditional printing? macro?
here's my situation, i have a workbook with many sheets in it, each sheet if the default print shortcut button was used would print to 4 pages of A4 paper. the data on these sheets varies everyday, someday's only 1 page per sheet would be full of data others 2, 3, or 4 etc. how could i write a macro that would be able to print only the needed pages?, each page, if it contains any data would have a value in a totals cell. so i would need code along these lines Sheets("sheet1").Select IF(OR(total1="",total1=0),"don't print","don't print"...

Automatically format
I have a workbook for personal banking purposes. When I log on to my bank, I copy the latest lines of my statement and paste into my workbook. I use Paste Special | Unicode text and my columns are filled appropriately. The only problem is that the last column, i.e. the balance, does not format to two decimal places as I had already set up. How can I automatically paste in the right format, please? Do I need a macro? After pasting your data: Select the entire column (click the column heading) Find the button on the "Home" tab (xl2007) or on a toolbar (xl2003) that looks somew...

How to apply a weighting
I'm trying to apply a weighting to my teams to allow for experience and size I have 3 teams which are different sizes and are different skill levels. Level 5 is the lowest skill level and level 1 being the highest skill level. Team size/level5/level4/level3/level2/level1 6/3/2/0/1/0 10/2/3/4/0/1 7/0/2/3/1/1 How do I apply a weighting to these numbers to allow for team size and experience. Can I do this using sumproduct? I tried looking at this but cant get my fragile little mind around it Thanks in advance. -- Homer J ---------------------------------------------------------------...

Strange Result using custom number format
I am trying to apply a custom number format that returns the following results.. If the number is less than -1 it is formatted as (?#) If the number is greather than +1 is is formatted as +?# If it is between -1 and 1 the result is text saying "Unchanged" But when I apply the following custom format--> [>1]+?#;[<-1] (?#); "Unch" I get a strange result for numbers between -1 and 1. I get -Unch. I can't figure out why it puts the - in front of the text. Thanks ...