Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gee Gee is offline
external usenet poster
 
Posts: 32
Default IF just STOPS WORKING

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default IF just STOPS WORKING

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gee Gee is offline
external usenet poster
 
Posts: 32
Default IF just STOPS WORKING

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.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default IF just STOPS WORKING

"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.


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gee Gee is offline
external usenet poster
 
Posts: 32
Default IF just STOPS WORKING

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gee Gee is offline
external usenet poster
 
Posts: 32
Default IF just STOPS WORKING

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gee Gee is offline
external usenet poster
 
Posts: 32
Default IF just STOPS WORKING

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default IF just STOPS WORKING

"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.



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default IF just STOPS WORKING

"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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gee Gee is offline
external usenet poster
 
Posts: 32
Default IF just STOPS WORKING

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.






  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default IF just STOPS WORKING

"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.





  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default IF just STOPS WORKING

=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.





  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default IF just STOPS WORKING

"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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default IF just STOPS WORKING

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.




  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gee Gee is offline
external usenet poster
 
Posts: 32
Default IF just STOPS WORKING

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.







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup stops working??? Steve Peel Excel Worksheet Functions 0 September 19th 08 07:08 PM
VLOOKUP stops working at row 13 Ann Scharpf Excel Worksheet Functions 4 August 23rd 07 03:45 PM
Autofilter Stops Working Vliegveld Excel Worksheet Functions 7 September 7th 05 01:19 AM
Validation List Stops working Brian Matlock Excel Discussion (Misc queries) 2 August 19th 05 08:07 PM
Hyperlink stops working Jami's Mom Excel Worksheet Functions 0 December 20th 04 05:17 PM


All times are GMT +1. The time now is 07:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"