Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Baffeling IF statement
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Baffeling IF statement
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. -- isabelle Le 2011-05-10 16:18, wabbleknee a écrit : 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Baffeling IF statement
On May 10, 1:18*pm, "wabbleknee" wrote:
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Baffeling IF statement
PS....
On May 10, 2:32*pm, I wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Baffeling IF statement
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 :o) "joeu2004" wrote in message ... On May 10, 1:18 pm, "wabbleknee" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Baffeling IF statement
On May 10, 4:31*pm, "wabbleknee" wrote:
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+(C7C9) -- 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+(C7C9))*1440,0)/1440) =IF(C9="","",--TEXT(C9-C7+(C7C9),"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". |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Baffeling IF statement
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. :o) "joeu2004" wrote in message ... On May 10, 4:31 pm, "wabbleknee" wrote: 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+(C7C9) -- 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+(C7C9))*1440,0)/1440) =IF(C9="","",--TEXT(C9-C7+(C7C9),"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". |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Baffeling IF statement
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. :o) "joeu2004" wrote in message ... On May 10, 4:31 pm, "wabbleknee" wrote: 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+(C7C9) -- 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+(C7C9))*1440,0)/1440) =IF(C9="","",--TEXT(C9-C7+(C7C9),"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". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup, if statement, maybe an and statement | Excel Discussion (Misc queries) | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions |