Conditional Formatting not matching fractional numbers correctly
Hi there,
I am trying to make a condition so that if a cell DOES NOT total to .00, .25, .50, .75 that the cell goes a colour (ideally yellow with red font).
I thought I found a formula to use in conditional formatting, =FLOOR(F9,0.25)<>F9 , but it does not work every time.
Are there any other suggestions out there? I have attached a copy of the sheet I am working with.
Thanks for any help anyone can provide!
Amanda
Last edited by Aholt; 03-20-2019 at 07:48 AM.
How are you calculating the number in F9? It is usually not reliable on any computer to do exact matches on floating-point numbers like this. It sometimes yields unexpected results. I won't get into a long explanation but if you explain more about what you are doing we might be able to get you a reliable method.
It might also help to attach your file. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.
Based on your description, this might work as your conditional format formula:
Please Login or Register to view this content.
Thank you for the feedback! I will post the sheet now
Use the following Rules for the third condition, :
Sample Payroll Sheet 1.xlsxPlease Login or Register to view this content.
THANK YOU SO MUCH!!Originally Posted by Saqib Qureshi
Use the following Rules for the third condition, :
Attachment 616385Please Login or Register to view this content.
Do you happen to know why if i put in columns J and K 4:30 PM - 5:30 PM why the total of 1 gets highlighted and red font? I noticed this yesterday before you so generously helped me out!
The answer to this goes back to my first post, which I don't think anyone noticed:
When you put the time 4:30 and 5:30, you see 1.00 displayed. But the actual value that is calculated is 0.999999999999999*. You can see this if you increase the number of decimals shown in the cell. So it does not actually end in .00 and your conditional formatting rule fails.
Your formula in column M is
=L4*24*100/100
To fix this, change it to
=ROUND(L4*24*100/100,2)
Do the same thing in column I.
__________________________
*Here is the short version: Excel stores dates and times as days from 1/1/1900. So 4:30 PM is stored as 16.5/24. This comes out exactly as 0.6875. But then you have 5:30, which is stored as 17.5/24, which cannot be expressed exactly as a decimal number. It is 0.72916666... (repeating 6). So when you do arithmetic on them, you don't get an exact answer.
This formula ended up rounding other amounts, which is not what I want to happen. I get what you are saying though.
It is a little strange because if i put 4:30 AM - 5:30 Am it totals to 1 and doesn't change the cell colour....if i have it 4:30 PM to 5:30 PM it highlights the cell yellow and font red.
You are only displaying two decimal places, so why is it a problem to round the value to two decimal places? The underlying value will exactly match what is displayed, which is not true in your original formulas.
It is not strange at all if you understand how the calculations are done.It is a little strange because if i put 4:30 AM - 5:30 Am it totals to 1 and doesn't change the cell colour....if i have it 4:30 PM to 5:30 PM it highlights the cell yellow and font red.
5:30 AM minus 4:30 AM = 0.0416666666666667
5:30 PM minus 4:30 PM = 0.0416666666666666
This is evident if you unhide column L and change the format to General, then increase the number of decimals displayed.
Therefore, when you multiply back times 24 you get different answers. This is the point I was trying to make about real numbers in a computer.
I have been in the software development industry for over 35 years and we have two standard solutions to comparing two real numbers:
1. Round
2. Test using a tolerance
In your case rounding is the only solution that makes sense, since you are dealing with time and you don't need high precision.
Sorry that i seem to frustrating you. I am new to using conditional formulas and am just trying to understand. I can't have 4:30 PM 4:35 PM round up to 1 hour, that is what I meant by I can't round up (that is what happened when I tried the formula you suggested).
I guess I don't understand this enough to use your help.
Thank you for your time. I guess I will try and explore some other avenues to get this sorted.
I really think I can help you here but I think I need to clarify how to do the rounding. If you have 4:30 PM and 4:35 PM, your result in column I using your existing formula is this:
noround.JPG
If you modify your formula to
(see post #7)Formula:
Please Login or Register to view this content.
then the result in column I is rounded to the nearest hundredth of an hour, not rounded up an hour.
round2.JPG
The number you see displayed is the same, but the underlying value now works with your conditional formatting rules.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks