Baffeling IF statement

[wabbleknee]

I am stumped on this one, so simple yet....

a1=9:00
b1=9:30
c1= the time difference with the formula =B1-A1 (results =0:30)
d1=0:30 (used as a reference or goal)
e1 formula =IF(c1=D1,"Yes","No") In this case the answer is YES

Now the hard part, if I change the cells to:
b1=9:50, c1 showes 0:50
D1=0:50
e1 is now = NO it should be YES

I tried test values of 0:49 and 0:51 by changing b1 d1 they work and say
YES, only 0:50 minutes errors????

TX MIke

 

[isabelle]

hi,

the result of the formula is 0.0347222222222223
and the value of 0:50 in standard is 0.0347222222222222
because the data is converted to hexadecimal, the subtraction is made ​​, and data are converted into decimal data.

 

[joeu2004]

a1=9:00 [....]
c1= [...] =B1-A1 [....]
e1 formula =IF(c1=D1,"Yes","No") [....]
b1=9:50, c1 showes 0:50
D1=0:50
e1 is now = NO  it should be YES

This is not as uncommon as you might think. For example,
=IF(10.1-10=0.1,TRUE) returns FALSE(!).

And sometimes =IF(A1=B1,TRUE) returns TRUE, but =IF(A1-B1=0,TRUE)
returns FALSE. Or =IF(A1+B1-C1=A1-C1+B1,TRUE) returns FALSE(!).

First, you need to understand that Excel time is stored as a fraction
of a day. So 1 hour is 1/24, 1 minute is 1/1440, and 1 second is
1/86400.

Second, most non-integer numbers cannot be represented exactly because
Excel (and most applications) usually uses binary floating-point to
represent numbers and perform arithmetic.

These little inaccuracies sometimes magnify noticable after performing
arithmetic.

One remedy is to always explicitly round any arithmetic that involes
non-integer numbers. Usually, we use ROUND (or some variant of it).
For time expressions, you might also use TEXT.

And it is best to round the cell that performs the computation. So
instead of fixing E1, fix C1 as follows:

=ROUND((B1-A1)*1440,0)/1440

=--TEXT(B1-A1,"hh:mm")

formatted as Time or a Custom format like [h]:mm.

The double-negative converts the text result to numeric.

 

[joeu2004]

PS....

This is not as uncommon as you might think. [....]
sometimes =IF(A1=B1,TRUE) returns TRUE, but
=IF(A1-B1=0,TRUE) returns FALSE.

And this is what is misleading you into think 9:49 and 9:51 work.
IF(C1=D1,"yes","no") returns "yes", but IF(C1-D1=0,"yes","no") returns
"no".

This demonstrates that C1 is not actually identical to D1. The
difference is so small that we cannot see it even if we format C1 and
D1 as Number with 16 decimal places in this case (i.e. to display 15
significant digits).

The reason for the different results of the two IF expressions is the
dubious heuristic poorly described under the misleading title "Example
When a Value Reaches Zero" at http://support.microsoft.com/kb/78113.

In short, Excel sometimes forces a result to be zero or a comparison
to be equal if Excel considers the result to be "close enough" to
zero. (MS does not explain what is considered "close enough".)

KB 78113 also attempts to explain the binary floating-point form and
its consequences. The explanation is not very good, IMHO. And it is
incorrect in several details. But it might give you some useful
insight.

 

[wabbleknee]

Thanks joeu2004. I suspected that it was a rounding error, but dismissed it
because it was only happening with 0:50. Surely, there has to be others
that are not an exact match from a pure number standpoint, that would not
give a response as "TRUE" or "YES" in my case. Will try the Round function,
but stand puzzled on how to implement that. My existing formula was severly
reduced in an attempt to solve the problem. As the original formula was
implemented, I always check the "input" for a blank and if so write a blank
and in addition, sometimes the hours run across the midnight hour. i.e
23:59 to 0:10 . So my formula looks like this: C7 time is when the clock
starts, c8 thru c15 are time elapse (goal measurements) from the starting
point. =IF(C9="","",MOD(C9-$C$7,1)) then I am checking the result against
a maximum value (<=goal) to see if we met that goal. So looking at my
original question, was the 0:50 minute goal met, Yes, however the status
column said NO. Tried several times to implement the ROUND function into
the original formula =IF(C9="","",MOD(C9-$C$7,1)) . Thanks for your
response, sounds like you been there before )

"joeu2004" wrote in message

a1=9:00 [....]
c1= [...] =B1-A1 [....]
e1 formula =IF(c1=D1,"Yes","No") [....]
b1=9:50, c1 showes 0:50
D1=0:50
e1 is now = NO it should be YES

This is not as uncommon as you might think. For example,
=IF(10.1-10=0.1,TRUE) returns FALSE(!).

And sometimes =IF(A1=B1,TRUE) returns TRUE, but =IF(A1-B1=0,TRUE)
returns FALSE. Or =IF(A1+B1-C1=A1-C1+B1,TRUE) returns FALSE(!).

First, you need to understand that Excel time is stored as a fraction
of a day. So 1 hour is 1/24, 1 minute is 1/1440, and 1 second is
1/86400.

Second, most non-integer numbers cannot be represented exactly because
Excel (and most applications) usually uses binary floating-point to
represent numbers and perform arithmetic.

These little inaccuracies sometimes magnify noticable after performing
arithmetic.

One remedy is to always explicitly round any arithmetic that involes
non-integer numbers. Usually, we use ROUND (or some variant of it).
For time expressions, you might also use TEXT.

And it is best to round the cell that performs the computation. So
instead of fixing E1, fix C1 as follows:

=ROUND((B1-A1)*1440,0)/1440

=--TEXT(B1-A1,"hh:mm")

formatted as Time or a Custom format like [h]:mm.

The double-negative converts the text result to numeric.

 

[joeu2004]

I suspected that it was a rounding error, but dismissed it
because it was only happening with 0:50.  Surely, there has
to be others that are not an exact match

It will depend on the hours of the day in A1 and B1, referring to your
original posting. It is difficult to predict. It is not difficult to
write a macro to determine "all" pairs that cause problems. But even
that might vary, depending on if/how you extract(?) the time from a
date/time specification. I'm suspicious because of your MOD usage
below.

No matter. The point is: __always__ expect that the match will
__not__ be exact and program accordingly.

wabbleknee wrote:
from a pure number

As the original formula was implemented, [....]
sometimes the hours run across the midnight hour.
i.e 23:59 to 0:10 . So my formula looks like this:
C7 time is when the clock starts, c8 thru c15 are
time elapse (goal measurements) from the starting
point.  =IF(C9="","",MOD(C9-$C$7,1)) then I am
checking the result against a maximum value
(<=goal) to see if we met that goal.

I must admit: it is unclear why you are using MOD and what
__exactly__ is in C7 and C9.

The best way to handle start/end times that might span across midnight
is to record date as well as time. You can also set the format to
display only time, if you wish. If you do this, elapsed time is
simply C9-C7 -- no need for MOD.

If you must record only time in C7 and C9, elapsed time is computed by
C9-C7+(C7>C9) -- again, no need for MOD. Note that this assumes that
C7 and C9 are within 24 hours of each other.

In either case, I think TEXT is easier to use than ROUND in this
context. But I will demonstrate both.

=IF(C9="","",ROUND((C9-C7+(C7>C9))*1440,0)/1440)

=IF(C9="","",--TEXT(C9-C7+(C7>C9),"hh:mm"))

I would format using Custom [h]:mm as "good practice".

The [h] notation will display hours > 24. But it appears you do not
expect that. So a Time option or Custom hh:mm might do just as well
for your purposes.

If that formula is in C10 and your goal is a constant in A1, you
should be able to write:

=IF(C10<=A1,"yes","no")

Alternatively, if your goal is 8 hours (e.g.), you could write any of
the following:

=IF(C10<=TIME(8,0,0),"yes","no")

=IF(C10<=--"08:00","yes","no")

The first form is preferred as "good practice".

 

[wabbleknee]

Tx again joeu2004. The procedure is much easier than you might expect. In
a medical environment, when a person is assessed with a stroke, certain
things must be completed within an hour. (The Golden Hour) C7 is the start
of that hour, entered simply as 12:30 c8, c9,c10,c11, c12,c13,c14,and
finally c15 (1 hour from c7). c8 thru c14 are different intervals. Data
is collected after the fact and saved to analyze. Conditional formatting
turns the elapsed time RED if it exceeds the interval. Let me implement
your suggested changes and test it over the next week or so. Currently
formatted as [h]:mm Intervals are in minutes, 10, 20, 25, 45, 45, 45, 50
and finally 60. Currently I have a "hidden helper column doing the
rounding" and have tested it with 4 other people, no problems as of yet!
Have been doing Excel for 20+ years, learn something new everyday. )

"joeu2004" wrote in message

I suspected that it was a rounding error, but dismissed it
because it was only happening with 0:50. Surely, there has
to be others that are not an exact match

It will depend on the hours of the day in A1 and B1, referring to your
original posting. It is difficult to predict. It is not difficult to
write a macro to determine "all" pairs that cause problems. But even
that might vary, depending on if/how you extract(?) the time from a
date/time specification. I'm suspicious because of your MOD usage
below.

No matter. The point is: __always__ expect that the match will
__not__ be exact and program accordingly.

wabbleknee wrote:
from a pure number

As the original formula was implemented, [....]
sometimes the hours run across the midnight hour.
i.e 23:59 to 0:10 . So my formula looks like this:
C7 time is when the clock starts, c8 thru c15 are
time elapse (goal measurements) from the starting
point. =IF(C9="","",MOD(C9-$C$7,1)) then I am
checking the result against a maximum value
(<=goal) to see if we met that goal.

I must admit: it is unclear why you are using MOD and what
__exactly__ is in C7 and C9.

The best way to handle start/end times that might span across midnight
is to record date as well as time. You can also set the format to
display only time, if you wish. If you do this, elapsed time is
simply C9-C7 -- no need for MOD.

If you must record only time in C7 and C9, elapsed time is computed by
C9-C7+(C7>C9) -- again, no need for MOD. Note that this assumes that
C7 and C9 are within 24 hours of each other.

In either case, I think TEXT is easier to use than ROUND in this
context. But I will demonstrate both.

=IF(C9="","",ROUND((C9-C7+(C7>C9))*1440,0)/1440)

=IF(C9="","",--TEXT(C9-C7+(C7>C9),"hh:mm"))

I would format using Custom [h]:mm as "good practice".

The [h] notation will display hours > 24. But it appears you do not
expect that. So a Time option or Custom hh:mm might do just as well
for your purposes.

If that formula is in C10 and your goal is a constant in A1, you
should be able to write:

=IF(C10<=A1,"yes","no")

Alternatively, if your goal is 8 hours (e.g.), you could write any of
the following:

=IF(C10<=TIME(8,0,0),"yes","no")

=IF(C10<=--"08:00","yes","no")

The first form is preferred as "good practice".

 

[wabbleknee]

Tx again joeu2004. The procedure is much easier than you might expect. In a
medical environment, when a person is assessed with a stroke, certain things
must be completed within an hour. (The Golden Hour) C7 is the start of that
hour, entered simply as 12:30 c8, c9,c10,c11, c12,c13,c14,and finally c15 (1
hour from c7). c8 thru c14 are different intervals. Data is collected after
the fact and saved to analyze. Conditional formatting turns the elapsed time
RED if it exceeds the interval. Let me implement your suggested changes and
test it over the next week or so. Currently formatted as [h]:mm Intervals
are in minutes, 10, 20, 25, 45, 45, 45, 50 and finally 60. Currently I have
a "hidden helper column doing the rounding" and have tested it with 4 other
people, no problems as of yet! Have been doing Excel for 20+ years, learn
something new everyday. )

"joeu2004" wrote in message

I suspected that it was a rounding error, but dismissed it
because it was only happening with 0:50. Surely, there has
to be others that are not an exact match

It will depend on the hours of the day in A1 and B1, referring to your
original posting. It is difficult to predict. It is not difficult to
write a macro to determine "all" pairs that cause problems. But even
that might vary, depending on if/how you extract(?) the time from a
date/time specification. I'm suspicious because of your MOD usage
below.

No matter. The point is: __always__ expect that the match will
__not__ be exact and program accordingly.

wabbleknee wrote:
from a pure number

As the original formula was implemented, [....]
sometimes the hours run across the midnight hour.
i.e 23:59 to 0:10 . So my formula looks like this:
C7 time is when the clock starts, c8 thru c15 are
time elapse (goal measurements) from the starting
point. =IF(C9="","",MOD(C9-$C$7,1)) then I am
checking the result against a maximum value
(<=goal) to see if we met that goal.

I must admit: it is unclear why you are using MOD and what
__exactly__ is in C7 and C9.

The best way to handle start/end times that might span across midnight
is to record date as well as time. You can also set the format to
display only time, if you wish. If you do this, elapsed time is
simply C9-C7 -- no need for MOD.

If you must record only time in C7 and C9, elapsed time is computed by
C9-C7+(C7>C9) -- again, no need for MOD. Note that this assumes that
C7 and C9 are within 24 hours of each other.

In either case, I think TEXT is easier to use than ROUND in this
context. But I will demonstrate both.

=IF(C9="","",ROUND((C9-C7+(C7>C9))*1440,0)/1440)

=IF(C9="","",--TEXT(C9-C7+(C7>C9),"hh:mm"))

I would format using Custom [h]:mm as "good practice".

The [h] notation will display hours > 24. But it appears you do not
expect that. So a Time option or Custom hh:mm might do just as well
for your purposes.

If that formula is in C10 and your goal is a constant in A1, you
should be able to write:

=IF(C10<=A1,"yes","no")

Alternatively, if your goal is 8 hours (e.g.), you could write any of
the following:

=IF(C10<=TIME(8,0,0),"yes","no")

=IF(C10<=--"08:00","yes","no")

The first form is preferred as "good practice".