Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weird Finding
I have a formula set up to calculate the difference between 2 times.
Cell A2 = 10:00 AM Cell B2 = 10:25 AM The formula in Cell A3 is =(B2-A2)*1440. This formula returns the total minutes between the two times into Cell A3 which the value is 25. I have conditional formatting set up to fill in the cell color as red if the value is less than 25. For some reason, the cell is filling in red for the calculation between 10:00 AM and 10:25 AM which is incorrect due to the value being exactly 25. However, if I do a calculation of 11:00 AM to 11:25 AM, the cell with the conditional formatting does not fill in with red which is correct because the value is exactly 25. I typed in some other ones too. I think 12 - 12:25 turned it red too. Its definitely weird. Can someone explain for me? Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weird Finding
"Chris" wrote:
The formula in Cell A3 is =(B2-A2)*1440. Try =ROUND((B2-A2)*1440,0) instead. I have conditional formatting set up to fill in the cell color as red if the value is less than 25. [....] Can someone explain for me? If you format your original A3 with 13 decimal places, you might get some insight: the value that __appears__ to be 25 is really 24.9...9, which is indeed less than 25. The reason is complicated to explain, but very common. As you may know, time is stored as a fraction of a day. Most fractions cannot be represented exactly by Excel (and most applications). Format A2 and B2 as Number with 15 decimal places, and you will see what I mean. These inherent inaccuracies are often magnified when you perform arithmetic operations. Generally, the solution is to round results of arithmetic operations. There are several ways to make that happen. Using formatting to round only changes the appearance of the value, not the actual value. It is best to round explicitly, either by using the ROUND function as I did above, or by setting the "Percision as displayed" option under Tools Options Calculation. I usually deprecate PAD for a number of reasons. ----- original message ----- "Chris" wrote: I have a formula set up to calculate the difference between 2 times. Cell A2 = 10:00 AM Cell B2 = 10:25 AM The formula in Cell A3 is =(B2-A2)*1440. This formula returns the total minutes between the two times into Cell A3 which the value is 25. I have conditional formatting set up to fill in the cell color as red if the value is less than 25. For some reason, the cell is filling in red for the calculation between 10:00 AM and 10:25 AM which is incorrect due to the value being exactly 25. However, if I do a calculation of 11:00 AM to 11:25 AM, the cell with the conditional formatting does not fill in with red which is correct because the value is exactly 25. I typed in some other ones too. I think 12 - 12:25 turned it red too. Its definitely weird. Can someone explain for me? Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Weird Finding
I think the problem is with the way XL stores time. 10:00 is actually stored
as a decimal number, which in this case ends with 6 repeating. When you do the subtraction, you end up with a decimal that has 1 repeating. Finally, XL does the multiplication, and due to a 15 digit limit, displays the number has 25. However, if you were to format the cell as number with 13 decimals, you can see that is shows: 24.9999999999999 Thus, it really is less than 25 (by a very small amount!) While 11:00 to 11:25 comes out to: 25.0000000000000 For a solution, you could try: =ROUND((B2-A2)*1440,0) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Chris" wrote: I have a formula set up to calculate the difference between 2 times. Cell A2 = 10:00 AM Cell B2 = 10:25 AM The formula in Cell A3 is =(B2-A2)*1440. This formula returns the total minutes between the two times into Cell A3 which the value is 25. I have conditional formatting set up to fill in the cell color as red if the value is less than 25. For some reason, the cell is filling in red for the calculation between 10:00 AM and 10:25 AM which is incorrect due to the value being exactly 25. However, if I do a calculation of 11:00 AM to 11:25 AM, the cell with the conditional formatting does not fill in with red which is correct because the value is exactly 25. I typed in some other ones too. I think 12 - 12:25 turned it red too. Its definitely weird. Can someone explain for me? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is this weird or what? | Excel Worksheet Functions | |||
Weird Multiplication! | Excel Worksheet Functions | |||
Weird files | New Users to Excel | |||
WEIRD QUESTION | Excel Discussion (Misc queries) | |||
Weird | New Users to Excel |