Another rounding issue
- From: "Biff" <biffinpitt@xxxxxxxxxxx>
- Date: Mon, 20 Jun 2005 02:08:54 -0400
Hi Folks!
Here's another rounding issue I just discovered:
A2 = 9:00 PM (not calculated, manually entered)
B2 = 10:00 PM (not calculated, manually entered)
C2 formula: =B2-A2+(B2<A2) Formatted as h:mm returns as expected 1:00
D2 formula: =IF(C2<1/24,1/24-C2,0)
Without preformatting cell D2 it defaults to GENERAL and unexpectedly
returns 3.46945E-17 which is the value_if_true argument of the IF function.
When formatted as h:mm it returns 0:00.
If cell D2 is formatted as h:mm and is tested to be = 0 FAILURE!
Here's how it breaks down:
=IF(0.0416666666666666<0.0416666666666667,1/24-C2,0)
=(B2-A2+(B2<A2))*24 returns 1 as expected. However, if formatted as number
to 15 decimal places returns 0.999999999999999. So, if that formula was
tested to return = 1 it would also fail.
But........ change:
A2 = 11:00 AM
B2 = 12:00 PM
(not drag filled either!) D2 returns 0 as expected and when tested for = 0
passes.
Hmmmm!
Just be aware!
Biff
.
- Follow-Ups:
- Re: Another rounding issue
- From: Jerry W. Lewis
- Re: Another rounding issue
- From: Aladin Akyurek
- Re: Another rounding issue
- Prev by Date: Re: Calculating Age
- Next by Date: how to set up one cell that assigns a value based on the value of another cell
- Previous by thread: SUMIF Alternative?
- Next by thread: Re: Another rounding issue
- Index(es):
Relevant Pages
|
