Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Why has this worked for about 3 hours and then suddenly stopped??
Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
The formula is fine with the cell reference Column HI Row 2..
and returns YES if HI2 is between 401 and 402 (eg: 401.1) =IF(AND(HI2401,HI2<402),"YES","NO") If this post helps click Yes --------------- Jacob Skaria "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
"Gee" wrote:
Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
It's just so weird.
It just stops working out of the blue. I guess it's just an Excel 07 thing? "Jacob Skaria" wrote: The formula is fine with the cell reference Column HI Row 2.. and returns YES if HI2 is between 401 and 402 (eg: 401.1) =IF(AND(HI2401,HI2<402),"YES","NO") If this post helps click Yes --------------- Jacob Skaria "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I thought it might be something like that. I changed the columns to no
decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! Thank you SO much for the help! I was really in a corner. "JoeU2004" wrote: "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#6
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
It stopped working again...here's my formula:
=IF(AND(401<HI2,HI2<402),"YES","NO") "Gee" wrote: I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! Thank you SO much for the help! I was really in a corner. "JoeU2004" wrote: "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#7
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hey, what about some kind of BETWEEN...will that work?
"Gee" wrote: It stopped working again...here's my formula: =IF(AND(401<HI2,HI2<402),"YES","NO") "Gee" wrote: I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! Thank you SO much for the help! I was really in a corner. "JoeU2004" wrote: "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#8
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
"Gee" wrote:
I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! I think you missed the point. Changing the format only changes the appearance of the cell value; it does not change the actual value. WYSI(not)WYG! You wrote previously: =IF(AND(HI2401,HI2<402),"YES","NO") If you are happy with displaying H12 with zero decimal places, it is no longer clear what your intent is with the AND() expression above. I suspect you want: =if(and(401<=round(H12,0),round(H12,0)<=402), "YES", "NO") ----- original message ----- "Gee" wrote in message ... I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! Thank you SO much for the help! I was really in a corner. "JoeU2004" wrote: "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#9
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I went back through the suggestions and totally missed extending to 13
decimal places..it looks like that might just work! The rounding didn't, but that out to 13 places looks like it will. Thank you SO much for your patience and brain. "JoeU2004" wrote: "Gee" wrote: I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! I think you missed the point. Changing the format only changes the appearance of the cell value; it does not change the actual value. WYSI(not)WYG! You wrote previously: =IF(AND(HI2401,HI2<402),"YES","NO") If you are happy with displaying H12 with zero decimal places, it is no longer clear what your intent is with the AND() expression above. I suspect you want: =if(and(401<=round(H12,0),round(H12,0)<=402), "YES", "NO") ----- original message ----- "Gee" wrote in message ... I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! Thank you SO much for the help! I was really in a corner. "JoeU2004" wrote: "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#10
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
You are talking about H12, but the OP was rerferring to HI2.
Beware of the difference between letter I and figure 1. -- David Biddulph "JoeU2004" wrote in message ... "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#11
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
=if(and(401<=round(HI2,0),round(HI2,0)<=402), "YES", "NO") would presumably
be equivalent to =if(or(round(HI2,0)=401,round(HI2,0)=402), "YES", "NO") or to =if(and(HI2=400.5,HI2<402.5), "YES", "NO") if either of those helps the OP to see whether that's what he wants. [I have changed your H12s to the OP's HI2 reference.] -- David Biddulph "JoeU2004" wrote in message ... "Gee" wrote: I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! I think you missed the point. Changing the format only changes the appearance of the cell value; it does not change the actual value. WYSI(not)WYG! You wrote previously: =IF(AND(HI2401,HI2<402),"YES","NO") If you are happy with displaying H12 with zero decimal places, it is no longer clear what your intent is with the AND() expression above. I suspect you want: =if(and(401<=round(H12,0),round(H12,0)<=402), "YES", "NO") ----- original message ----- "Gee" wrote in message ... I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! Thank you SO much for the help! I was really in a corner. "JoeU2004" wrote: "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#12
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I caught that right off and just changed it.
It's working very well now! "David Biddulph" wrote: You are talking about H12, but the OP was rerferring to HI2. Beware of the difference between letter I and figure 1. -- David Biddulph "JoeU2004" wrote in message ... "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#13
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
"David Biddulph" <groups [at] biddulph.org.uk wrote:
=if(and(401<=round(HI2,0),round(HI2,0)<=402), "YES", "NO") would presumably be equivalent to =if(or(round(HI2,0)=401,round(HI2,0)=402), "YES", "NO") Yes, I thought of that, too. I chose not to mention it because I did not want to confuse Gee anymore that he/she already seems to be. I chose the more-general numeric range test because I wondered if integer limits are really what Gee wants/needs. or to =if(and(HI2=400.5,HI2<402.5), "YES", "NO") I disagree, especially considering Gee's continued confusion between displayed and actual values. Depending on formatting, a cell might display as 400.5 (expect "YES"), but it is actually 400.49 (resulting in "NO"). People should learn not to compare with numbers with decimal fractions, since most decimal fractions are not exact internally. They will inevitably get a surprising result. ----- original message ----- "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =if(and(401<=round(HI2,0),round(HI2,0)<=402), "YES", "NO") would presumably be equivalent to =if(or(round(HI2,0)=401,round(HI2,0)=402), "YES", "NO") or to =if(and(HI2=400.5,HI2<402.5), "YES", "NO") if either of those helps the OP to see whether that's what he wants. [I have changed your H12s to the OP's HI2 reference.] -- David Biddulph "JoeU2004" wrote in message ... "Gee" wrote: I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! I think you missed the point. Changing the format only changes the appearance of the cell value; it does not change the actual value. WYSI(not)WYG! You wrote previously: =IF(AND(HI2401,HI2<402),"YES","NO") If you are happy with displaying H12 with zero decimal places, it is no longer clear what your intent is with the AND() expression above. I suspect you want: =if(and(401<=round(H12,0),round(H12,0)<=402), "YES", "NO") ----- original message ----- "Gee" wrote in message ... I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! Thank you SO much for the help! I was really in a corner. "JoeU2004" wrote: "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#14
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
"Gee" wrote:
Hey, what about some kind of BETWEEN...will that work? Excel does not have a "between" operator. But that is exactly what AND(401<HI2,HI2<402) means. By the way, that is also why I write in that order instead of AND(HI2401,HI2<402). Even thought the two forms are identical in effect, the first form looks like a "between" operation. In any case, the real point is: you have never said in English what it is you are trying to accomplish with the AND() expression. By testing AND(401<HI2,HI2<402), you seem to be trying to look for numbers with decimal fractions. But you have never articulated; and some of your postings seem to contradict that assumption. ----- original message ----- "Gee" wrote in message ... Hey, what about some kind of BETWEEN...will that work? "Gee" wrote: It stopped working again...here's my formula: =IF(AND(401<HI2,HI2<402),"YES","NO") "Gee" wrote: I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! Thank you SO much for the help! I was really in a corner. "JoeU2004" wrote: "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#15
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
"Gee" wrote:
I went back through the suggestions and totally missed extending to 13 decimal places..it looks like that might just work! Changing the format has nothing to do with the correctness or not of the AND() expression. I suggested formatting to 13 decimal places merely as a diagnostic approach -- a way for you to show us what data the IF() formula "fails" on -- keeping in mind that you never answered my question, namely: what results represent "failure", and what results represent "working", and with what data? The IF() formula sometimes works and sometimes fails because it is apparently fundamentally flawed. Failure and success is presumably due to changes in data. If you told us what the data looked like and, in English, what you are trying to accomplish with the IF() formula, we might be more effective in showing you how to write it so that it works for all data that you will encounter. But I think I am only confusing your further. I suggest that you "start over" -- in this thread, not in another thread. State what you are currently doing, since it might have morphed by now (mea culpa!), along with everything else I suggested above. PS: Sorry about the confusion between HI2 and H12. Weary eyes. Also, H12 seemed more likely. But okay: HI2 it is. ----- original message ----- "Gee" wrote in message ... I went back through the suggestions and totally missed extending to 13 decimal places..it looks like that might just work! The rounding didn't, but that out to 13 places looks like it will. Thank you SO much for your patience and brain. "JoeU2004" wrote: "Gee" wrote: I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! I think you missed the point. Changing the format only changes the appearance of the cell value; it does not change the actual value. WYSI(not)WYG! You wrote previously: =IF(AND(HI2401,HI2<402),"YES","NO") If you are happy with displaying H12 with zero decimal places, it is no longer clear what your intent is with the AND() expression above. I suspect you want: =if(and(401<=round(H12,0),round(H12,0)<=402), "YES", "NO") ----- original message ----- "Gee" wrote in message ... I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! Thank you SO much for the help! I was really in a corner. "JoeU2004" wrote: "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#16
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Errata....
I wrote: I suggested formatting to 13 decimal places merely as a diagnostic approach I don't know why I suggested 13 dp. With numbers in the range of 401-402, it only makes sense to format with up to 12 decimal places. keeping in mind that you never answered my question, namely: what results represent "failure", and what results represent "working", and with what data? Let me offer a hypothetical example. "The IF() formula returns YES even though the cell displays 401 or 402. I am expecting NO. I want YES only when the value is between 401 and 402 non-inclusively; that is, values like 401.1, 401.2, etc." Good luck! ----- original message ----- "JoeU2004" wrote in message ... "Gee" wrote: I went back through the suggestions and totally missed extending to 13 decimal places..it looks like that might just work! Changing the format has nothing to do with the correctness or not of the AND() expression. I suggested formatting to 13 decimal places merely as a diagnostic approach -- a way for you to show us what data the IF() formula "fails" on -- keeping in mind that you never answered my question, namely: what results represent "failure", and what results represent "working", and with what data? The IF() formula sometimes works and sometimes fails because it is apparently fundamentally flawed. Failure and success is presumably due to changes in data. If you told us what the data looked like and, in English, what you are trying to accomplish with the IF() formula, we might be more effective in showing you how to write it so that it works for all data that you will encounter. But I think I am only confusing your further. I suggest that you "start over" -- in this thread, not in another thread. State what you are currently doing, since it might have morphed by now (mea culpa!), along with everything else I suggested above. PS: Sorry about the confusion between HI2 and H12. Weary eyes. Also, H12 seemed more likely. But okay: HI2 it is. ----- original message ----- "Gee" wrote in message ... I went back through the suggestions and totally missed extending to 13 decimal places..it looks like that might just work! The rounding didn't, but that out to 13 places looks like it will. Thank you SO much for your patience and brain. "JoeU2004" wrote: "Gee" wrote: I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! I think you missed the point. Changing the format only changes the appearance of the cell value; it does not change the actual value. WYSI(not)WYG! You wrote previously: =IF(AND(HI2401,HI2<402),"YES","NO") If you are happy with displaying H12 with zero decimal places, it is no longer clear what your intent is with the AND() expression above. I suspect you want: =if(and(401<=round(H12,0),round(H12,0)<=402), "YES", "NO") ----- original message ----- "Gee" wrote in message ... I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! Thank you SO much for the help! I was really in a corner. "JoeU2004" wrote: "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#17
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
PS....
I wrote: "David Biddulph" <groups [at] biddulph.org.uk wrote: or to =if(and(HI2=400.5,HI2<402.5), "YES", "NO") [....] People should learn not to compare with numbers with decimal fractions, since most decimal fractions are not exact internally. They will inevitably get a surprising result. But using ROUND in comparisons with numbers with decimal fractions is okay, when done correctly. For example: =if(and(400.5<=round(HI2,1),round(HI2,1)<402.5), "YES", "NO") This is okay because ROUND ensures that the internal binary representation of its result exactly matches a constant with the same number of decimal places. For example, if A1 is =10.1-10, =(A1=0.1) returns FALSE, but =(round(A1,1)=0.1) returns TRUE. The reason is: the binary representation of 10.1-10 is not close enough to the binary representation of the constant 0.1 for Excel to consider them equal. In contrast, if A1 is =0.1+2^-56, =(A1=0.1) returns TRUE even though the binary representations of A1 and 0.1 are not the same. Likewise, =A1-0.1 returns zero. But =(A1-0.1) returns non-zero, namely about 1.39E-17. And =IF(A1-0.1=0,TRUE) returns FALSE, which flies in the face of reason considering the result of =A1-0.1, until we understand Excel's dubious heuristic for determining "close enough". But I digress.... ----- original message ----- "JoeU2004" wrote in message ... "David Biddulph" <groups [at] biddulph.org.uk wrote: =if(and(401<=round(HI2,0),round(HI2,0)<=402), "YES", "NO") would presumably be equivalent to =if(or(round(HI2,0)=401,round(HI2,0)=402), "YES", "NO") Yes, I thought of that, too. I chose not to mention it because I did not want to confuse Gee anymore that he/she already seems to be. I chose the more-general numeric range test because I wondered if integer limits are really what Gee wants/needs. or to =if(and(HI2=400.5,HI2<402.5), "YES", "NO") I disagree, especially considering Gee's continued confusion between displayed and actual values. Depending on formatting, a cell might display as 400.5 (expect "YES"), but it is actually 400.49 (resulting in "NO"). People should learn not to compare with numbers with decimal fractions, since most decimal fractions are not exact internally. They will inevitably get a surprising result. ----- original message ----- "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =if(and(401<=round(HI2,0),round(HI2,0)<=402), "YES", "NO") would presumably be equivalent to =if(or(round(HI2,0)=401,round(HI2,0)=402), "YES", "NO") or to =if(and(HI2=400.5,HI2<402.5), "YES", "NO") if either of those helps the OP to see whether that's what he wants. [I have changed your H12s to the OP's HI2 reference.] -- David Biddulph "JoeU2004" wrote in message ... "Gee" wrote: I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! I think you missed the point. Changing the format only changes the appearance of the cell value; it does not change the actual value. WYSI(not)WYG! You wrote previously: =IF(AND(HI2401,HI2<402),"YES","NO") If you are happy with displaying H12 with zero decimal places, it is no longer clear what your intent is with the AND() expression above. I suspect you want: =if(and(401<=round(H12,0),round(H12,0)<=402), "YES", "NO") ----- original message ----- "Gee" wrote in message ... I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! Thank you SO much for the help! I was really in a corner. "JoeU2004" wrote: "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#18
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
OK, all that said and my subsequent confusion, I never had a problem with
this kind of thing in '03, only in '07 and I'm still working on another problem with it but the 13 decimal places solved the Yes/No problem just fine. Thanks so much for your help. If you know anything about Excel VBA Programming, I'm having another problem with this project...It's under Excel Programming and the topic is "Sending 3 email?". You guys are good...I'd appreciate if you could give me some input on this very last hurdle. :) "JoeU2004" wrote: PS.... I wrote: "David Biddulph" <groups [at] biddulph.org.uk wrote: or to =if(and(HI2=400.5,HI2<402.5), "YES", "NO") [....] People should learn not to compare with numbers with decimal fractions, since most decimal fractions are not exact internally. They will inevitably get a surprising result. But using ROUND in comparisons with numbers with decimal fractions is okay, when done correctly. For example: =if(and(400.5<=round(HI2,1),round(HI2,1)<402.5), "YES", "NO") This is okay because ROUND ensures that the internal binary representation of its result exactly matches a constant with the same number of decimal places. For example, if A1 is =10.1-10, =(A1=0.1) returns FALSE, but =(round(A1,1)=0.1) returns TRUE. The reason is: the binary representation of 10.1-10 is not close enough to the binary representation of the constant 0.1 for Excel to consider them equal. In contrast, if A1 is =0.1+2^-56, =(A1=0.1) returns TRUE even though the binary representations of A1 and 0.1 are not the same. Likewise, =A1-0.1 returns zero. But =(A1-0.1) returns non-zero, namely about 1.39E-17. And =IF(A1-0.1=0,TRUE) returns FALSE, which flies in the face of reason considering the result of =A1-0.1, until we understand Excel's dubious heuristic for determining "close enough". But I digress.... ----- original message ----- "JoeU2004" wrote in message ... "David Biddulph" <groups [at] biddulph.org.uk wrote: =if(and(401<=round(HI2,0),round(HI2,0)<=402), "YES", "NO") would presumably be equivalent to =if(or(round(HI2,0)=401,round(HI2,0)=402), "YES", "NO") Yes, I thought of that, too. I chose not to mention it because I did not want to confuse Gee anymore that he/she already seems to be. I chose the more-general numeric range test because I wondered if integer limits are really what Gee wants/needs. or to =if(and(HI2=400.5,HI2<402.5), "YES", "NO") I disagree, especially considering Gee's continued confusion between displayed and actual values. Depending on formatting, a cell might display as 400.5 (expect "YES"), but it is actually 400.49 (resulting in "NO"). People should learn not to compare with numbers with decimal fractions, since most decimal fractions are not exact internally. They will inevitably get a surprising result. ----- original message ----- "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =if(and(401<=round(HI2,0),round(HI2,0)<=402), "YES", "NO") would presumably be equivalent to =if(or(round(HI2,0)=401,round(HI2,0)=402), "YES", "NO") or to =if(and(HI2=400.5,HI2<402.5), "YES", "NO") if either of those helps the OP to see whether that's what he wants. [I have changed your H12s to the OP's HI2 reference.] -- David Biddulph "JoeU2004" wrote in message ... "Gee" wrote: I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! I think you missed the point. Changing the format only changes the appearance of the cell value; it does not change the actual value. WYSI(not)WYG! You wrote previously: =IF(AND(HI2401,HI2<402),"YES","NO") If you are happy with displaying H12 with zero decimal places, it is no longer clear what your intent is with the AND() expression above. I suspect you want: =if(and(401<=round(H12,0),round(H12,0)<=402), "YES", "NO") ----- original message ----- "Gee" wrote in message ... I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! Thank you SO much for the help! I was really in a corner. "JoeU2004" wrote: "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#19
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
You guys have been so much help, I wonder if you know about Excel Programming
too? I'm nearly done with this project. This is where I am. Every thing works except it sends 3 emails instead of just one because I'm using "Calculate" It is a spreadsheet that is unmonitored and linked to an Access database and refreshes every minute. Maybe some kind of hack/crack that moves down one cell to "fake" a SelectionChange? Anyone know how I can do that? Private Sub Worksheet_Calculate() If Range("HK2").Value = "YES" Then Set aOutlook = GetObject(, "Outlook.Application") Set aEmail = aOutlook.CreateItem(0) aEmail.Importance = 2 aEmail.Subject = "TEST NOC AGING CALL NUMBER" aEmail.Body = Range("A2") aEmail.Recipients.Add " aEmail.Send End If End Sub |
#20
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Excel 2007 has different ways of guessing whether a difference is zero.
It's one of its known problems, and you'll see frequent reports in the archives of this and other excel newsgroups. -- David Biddulph "Gee" wrote in message ... OK, all that said and my subsequent confusion, I never had a problem with this kind of thing in '03, only in '07 and I'm still working on another problem with it but the 13 decimal places solved the Yes/No problem just fine. Thanks so much for your help. .... "JoeU2004" wrote: .... But using ROUND in comparisons with numbers with decimal fractions is okay, when done correctly. For example: =if(and(400.5<=round(HI2,1),round(HI2,1)<402.5), "YES", "NO") This is okay because ROUND ensures that the internal binary representation of its result exactly matches a constant with the same number of decimal places. For example, if A1 is =10.1-10, =(A1=0.1) returns FALSE, but =(round(A1,1)=0.1) returns TRUE. The reason is: the binary representation of 10.1-10 is not close enough to the binary representation of the constant 0.1 for Excel to consider them equal. In contrast, if A1 is =0.1+2^-56, =(A1=0.1) returns TRUE even though the binary representations of A1 and 0.1 are not the same. Likewise, =A1-0.1 returns zero. But =(A1-0.1) returns non-zero, namely about 1.39E-17. And =IF(A1-0.1=0,TRUE) returns FALSE, which flies in the face of reason considering the result of =A1-0.1, until we understand Excel's dubious heuristic for determining "close enough". .... |
#21
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
"Gee" wrote:
OK, all that said and my subsequent confusion, I never had a problem with this kind of thing in '03, only in '07 and I'm still working on another problem with it but the 13 decimal places solved the Yes/No problem just fine. Thanks so much for your help. No thanks is warranted, at least not to me. I feel quite guilty insofar as I have become complicit in your false sense of security. I have tried to explain repeatedly that I have offered no solution to a problem because I still do not know what the problem is. If there is a difference related to '07 v. '03, I suspect it might be in the database application (Access?) -- or perhaps it is merely a schema change that occurred during the transition by coincidence. David says there are differences in the way that Excel interprets arithmetic results. I'll take his word for it. But if there is a difference, any changes you made (I don't know what they are) might simply have hidden the problem or made it less likely, but not impossible, to occur. It is unclear to me how you used the "suggestion" (not!) to format to "13" (should be 12) decimal places and why you think it solved any problem. I can only ass-u-me that you mean that it changed your perception of the values and, hence, your expectation of the result of the Yes/No IF() formula. But I really must stop myself from making wild speculation (WAGs); it only contributes to the confusion here. You continue to offer insufficient details for even an intelligence guess as to what the problem is, much less how to solve it. However, I am curious. So if you post here again, it would be nice if you provided a concrete example that demonstrates how formatting to "13" decimal places seemed to have solved the problem -- and what you mean by "solved". ----- original message ----- "Gee" wrote in message ... OK, all that said and my subsequent confusion, I never had a problem with this kind of thing in '03, only in '07 and I'm still working on another problem with it but the 13 decimal places solved the Yes/No problem just fine. Thanks so much for your help. If you know anything about Excel VBA Programming, I'm having another problem with this project...It's under Excel Programming and the topic is "Sending 3 email?". You guys are good...I'd appreciate if you could give me some input on this very last hurdle. :) "JoeU2004" wrote: PS.... I wrote: "David Biddulph" <groups [at] biddulph.org.uk wrote: or to =if(and(HI2=400.5,HI2<402.5), "YES", "NO") [....] People should learn not to compare with numbers with decimal fractions, since most decimal fractions are not exact internally. They will inevitably get a surprising result. But using ROUND in comparisons with numbers with decimal fractions is okay, when done correctly. For example: =if(and(400.5<=round(HI2,1),round(HI2,1)<402.5), "YES", "NO") This is okay because ROUND ensures that the internal binary representation of its result exactly matches a constant with the same number of decimal places. For example, if A1 is =10.1-10, =(A1=0.1) returns FALSE, but =(round(A1,1)=0.1) returns TRUE. The reason is: the binary representation of 10.1-10 is not close enough to the binary representation of the constant 0.1 for Excel to consider them equal. In contrast, if A1 is =0.1+2^-56, =(A1=0.1) returns TRUE even though the binary representations of A1 and 0.1 are not the same. Likewise, =A1-0.1 returns zero. But =(A1-0.1) returns non-zero, namely about 1.39E-17. And =IF(A1-0.1=0,TRUE) returns FALSE, which flies in the face of reason considering the result of =A1-0.1, until we understand Excel's dubious heuristic for determining "close enough". But I digress.... ----- original message ----- "JoeU2004" wrote in message ... "David Biddulph" <groups [at] biddulph.org.uk wrote: =if(and(401<=round(HI2,0),round(HI2,0)<=402), "YES", "NO") would presumably be equivalent to =if(or(round(HI2,0)=401,round(HI2,0)=402), "YES", "NO") Yes, I thought of that, too. I chose not to mention it because I did not want to confuse Gee anymore that he/she already seems to be. I chose the more-general numeric range test because I wondered if integer limits are really what Gee wants/needs. or to =if(and(HI2=400.5,HI2<402.5), "YES", "NO") I disagree, especially considering Gee's continued confusion between displayed and actual values. Depending on formatting, a cell might display as 400.5 (expect "YES"), but it is actually 400.49 (resulting in "NO"). People should learn not to compare with numbers with decimal fractions, since most decimal fractions are not exact internally. They will inevitably get a surprising result. ----- original message ----- "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... =if(and(401<=round(HI2,0),round(HI2,0)<=402), "YES", "NO") would presumably be equivalent to =if(or(round(HI2,0)=401,round(HI2,0)=402), "YES", "NO") or to =if(and(HI2=400.5,HI2<402.5), "YES", "NO") if either of those helps the OP to see whether that's what he wants. [I have changed your H12s to the OP's HI2 reference.] -- David Biddulph "JoeU2004" wrote in message ... "Gee" wrote: I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! I think you missed the point. Changing the format only changes the appearance of the cell value; it does not change the actual value. WYSI(not)WYG! You wrote previously: =IF(AND(HI2401,HI2<402),"YES","NO") If you are happy with displaying H12 with zero decimal places, it is no longer clear what your intent is with the AND() expression above. I suspect you want: =if(and(401<=round(H12,0),round(H12,0)<=402), "YES", "NO") ----- original message ----- "Gee" wrote in message ... I thought it might be something like that. I changed the columns to no decimal places, but since it was getting data from an external database it might have caused the problem. It's working right now...if it stops I'll be back! Thank you SO much for the help! I was really in a corner. "JoeU2004" wrote: "Gee" wrote: Why has this worked for about 3 hours and then suddenly stopped?? Define "working". =IF(AND(HI2401,HI2<402),"YES","NO") It probably has nothing to do with time of day, unless the value in H12 is derived from time of day (i.e. NOW()). More likely, the value in H12 is not what it appears to be. For example, if the value is 401.00001, it might be displayed as 401, so you might expect "NO". But AND(401<H12,H12<402) returns TRUE, so you actual get "YES". If that is not enough to help you, post again with details, namely the formula and value in H12, formatted to 13 decimal places. ----- original message ----- "Gee" wrote in message ... Why has this worked for about 3 hours and then suddenly stopped?? Excel 2007 is what I'm using =IF(AND(HI2401,HI2<402),"YES","NO") Thank you in advance for any help you can give me. |
#22
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
"David Biddulph" <groups [at] biddulph.org.uk wrote:
Excel 2007 has different ways of guessing whether a difference is zero. It's one of its known problems, and you'll see frequent reports in the archives of this and other excel newsgroups. Oh? I do not remember seeing any threads where the root cause was found to be a difference in the way that Excel 2007 interprets arithmetic results. Can you point me to one, for my edification? I do remember many threads related to some functions (XIRR, NPV, YIELD, even EDATE!) returning errors reportedly in Excel 2007, but not in Excel 2003, allegedly with no changes other than to open the workbook in Excel 2007. I do not believe the root cause was ever agreed upon. For example, see http://www.google.com/url?url=http:/...0NeOfRxIe2h2jg . Is that you're thinking of? I'm not trying to impugn your statement. I'm just very curious about this, having spent a lot of time reverse-engineering the dubious heuristic as it is implemented in Excel 2003. ----- original message ----- "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Excel 2007 has different ways of guessing whether a difference is zero. It's one of its known problems, and you'll see frequent reports in the archives of this and other excel newsgroups. -- David Biddulph "Gee" wrote in message ... OK, all that said and my subsequent confusion, I never had a problem with this kind of thing in '03, only in '07 and I'm still working on another problem with it but the 13 decimal places solved the Yes/No problem just fine. Thanks so much for your help. ... "JoeU2004" wrote: ... But using ROUND in comparisons with numbers with decimal fractions is okay, when done correctly. For example: =if(and(400.5<=round(HI2,1),round(HI2,1)<402.5), "YES", "NO") This is okay because ROUND ensures that the internal binary representation of its result exactly matches a constant with the same number of decimal places. For example, if A1 is =10.1-10, =(A1=0.1) returns FALSE, but =(round(A1,1)=0.1) returns TRUE. The reason is: the binary representation of 10.1-10 is not close enough to the binary representation of the constant 0.1 for Excel to consider them equal. In contrast, if A1 is =0.1+2^-56, =(A1=0.1) returns TRUE even though the binary representations of A1 and 0.1 are not the same. Likewise, =A1-0.1 returns zero. But =(A1-0.1) returns non-zero, namely about 1.39E-17. And =IF(A1-0.1=0,TRUE) returns FALSE, which flies in the face of reason considering the result of =A1-0.1, until we understand Excel's dubious heuristic for determining "close enough". ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Vlookup stops working??? | Excel Worksheet Functions | |||
VLOOKUP stops working at row 13 | Excel Worksheet Functions | |||
Autofilter Stops Working | Excel Worksheet Functions | |||
Validation List Stops working | Excel Discussion (Misc queries) | |||
Hyperlink stops working | Excel Worksheet Functions |