 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2008error with conditional formatting
I am having trouble with some conditional formatting of times. I set up a rule so that if a value in D2:M2 is less than or equal to the value in C2, it will be highlighted (yellow background). The problem is that C2 is based on an average. For example, C2 says 0:43.2, but it is actually 0:43.19. Cell J2 is 0:43.2 (exactly), and I want it to be highlighted, but it isn't. If it helps, here is more detail on the workbook setup... Sheet1 through Sheet3 have times to the tenth of a second in D2 through M2 (i.e., m:ss.0), and N2=AVERAGE(D2:M2). On Sheet4, I calculate the minimum of the averages in C2=MIN('Previous:May-08'!N2). Sheet4 is the one with the conditional formatting. Thanks, ~ Horatio Format your cells as mm:ss.00 and then you will get to the second decimal point so you can se if it is actually less than or equal to C2. edvwvw >I am having trouble with some conditional formatting of times. I set up a >rule so that if a value in D2:M2 is less than or equal to the value in C2, it [quoted text clipped - 10 lines] >Thanks, >~ Horatio I'm only working with times to the nearest tenth of a second, so I need the number to behave as it is displayed (0:43.19 would round up to 0:43.2, and I need it to act as if it is exactly 43.2). ~ Horatio > Format your cells as mm:ss.00 and then you will get to the second decimal > point so you can se if it is actually less than or equal to C2. [quoted text clipped - 15 lines] > >Thanks, > >~ Horatio You could use the ROUND function (or MROUND) in your CF condition formula. -- David Biddulph >I am having trouble with some conditional formatting of times. I set up a > rule so that if a value in D2:M2 is less than or equal to the value in C2, [quoted text clipped - 14 lines] > Thanks, > ~ Horatio I tried playing around with ROUND and MROUND, but I'm stumped. I tried ROUND(C18,6), and that fixed my initial problem, but created problems in other cells. I also tried ROUND(C18,5) and ROUND(C18,7) - both of which had problems. I then tried MROUND. The value for one tenth of a second (i.e., 0:00.1) is 0.00000115740740740741... So to round to the nearest tenth of a second I tried MROUND(C18,0.00000115740740740741), and it didn't fix the problem. Is there a way to round directly to a tenth of a second, instead of using the long decimal value? Or am I doing it wrong? ~ Horatio > You could use the ROUND function (or MROUND) in your CF condition formula. > -- [quoted text clipped - 18 lines] > > Thanks, > > ~ Horatio =MROUND(C18,1/24/36000) or =MROUND(C18,1/864000) -- David Biddulph >I tried playing around with ROUND and MROUND, but I'm stumped. > [quoted text clipped - 42 lines] >> > Thanks, >> > ~ Horatio That's slick - I didn't think of using fractions. I had trouble using it in the CF condition formula, though. The formula I used was "J18<=MROUND($C18,1/24/36000)" and I got an error saying that you can't reference other worksheets or workbooks in a conditional format. I used the MROUND function on C18 instead, and it worked fine. Thanks for the help! ~ Horatio > =MROUND(C18,1/24/36000) or =MROUND(C18,1/864000) > -- [quoted text clipped - 46 lines] > >> > Thanks, > >> > ~ Horatio I think the problem with CF is that MROUND is in the Analysis ToolPak, and CF treats that as being another workshhet or workbook. -- David Biddulph > That's slick - I didn't think of using fractions. I had trouble using it > in [quoted text clipped - 68 lines] >> >> > Thanks, >> >> > ~ Horatio Thanks for your help. ~ Horatio > I think the problem with CF is that MROUND is in the Analysis ToolPak, and > CF treats that as being another workshhet or workbook. [quoted text clipped - 73 lines] > >> >> > Thanks, > >> >> > ~ Horatio |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.