 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / July 2006Excel Formula
Hello Programmers! I have a formula that checks to see if a certain value is in between two numbers: =IF(W44>35,IF(W44<37.5,\"EMPTY\")) Is there another way to (more simple) to write this formula. Thanks, EMoe  SignatureEMoe > I have a formula that checks to see if a certain value is in between > two numbers: > =IF(W44>35,IF(W44<37.5,\"EMPTY\")) > Is there another way to (more simple) to write this formula. I don't know if my other way is "more simple", but it fixes the fact that your formula returns FALSE if the conditions fail. But because of the incompleteness of your formula, is unclear exactly how to rewrite it. The following is a paradigm, which you need to modify: =if(and(35<W44, W44<37.5), "in between", "not in between") Obviously, you should replace "in between" and "not in between" with appropriate results. If you want the cell to appear empty, type "". By the way, I wonder if you really want "<=" instead of "<". PS: You can write W44>35 instead of 35<W44. I write it that way because it more closely mimics what we write mathematically, e.g. 35 < W44 < 37.5. Thanks for the reply: I don't think I explained myself well at first. This is what I have: 41 40 39 38 What I want to write is; if cell A1 is less than 38, say empty. If A1 is greater than 38, but less than 39, say one quarter. If A1 is greater than 39, but less than 40, say half. If A1 is greater than 40, but less than 41, say three quarters. If A1 is greater than 41, say full. All of this if possible in one formula. Thanks Again, EMoe SignatureEMoe Did you read what you posted? What do you want to happen if A1 *exactly* matches 38, 39, 40, and 41? SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > > Thanks for the reply: [quoted text clipped - 16 lines] > Thanks Again, > EMoe > I don't think I explained myself well at first. That is an understatement! ;-) > What I want to write is; if cell A1 is less than 38, say empty. If A1 > is greater than 38, but less than 39, say one quarter. If A1 is greater > than 39, but less than 40, say half. If A1 is greater than 40, but less > than 41, say three quarters. If A1 is greater than 41, say full. I will assume that you mean "greater than or equal to" whenever you say "greater than". =if(A1<38, "empty", if(A1<39, "quarter", if(A1<40, "half", if(A1<41, "three quarters", "full")))) A more concise way to formulate the same thing is: =if( A1<38, "empty", lookup(A1-38, {0,1,2,3}, {"quarter","half","three quarters","full"}) ) Thanks for all of you all's help. JoeU, I tried your formula, and it worked out fine. Also in the process of trying to figure this out, I came up with this, which also worked for me. Formula: =IF(AND(W44>0.5,W44<38.5),"Empty",IF(AND(W44>38.5,W44<39.5),"Low",IF(AND(W44>39.5,W44<40.5),"Ok",IF(W44>40.5,"Full",IF(W44<0.5,""))))) Thanks Again! EMoe SignatureEMoe > JoeU, I tried your formula, and it worked out fine. Also in the process > of trying to figure this out, I came up with this, which also worked for > me. Formula: > =IF(AND(W44>0.5,W44<38.5),"Empty",IF(AND(W44>38.5,W44<39.5),"Low",IF(AND(W44>39.5,W44<40.5),"Ok",IF(W44>40.5,"Full",IF(W44<0.5,""))))) In your original posting, you asked for simpler ways to do things. There are several things that can be improved (or corrected) in the above. 1. You repeatedly make the mistake of using "<" of ">" instead of "<=" or ">=", as I and others noted previously. That creates discontinuities -- values within the range of interest that are not covered. I doubt that you are doing that intentionally. For example, in the above, you might be surprised to get FALSE instead of "empty" or "low" when W44 is exactly 38.5. Similarly, you will get FALSE when W44 is exactly 39.5, 40.5 and 0.5. 2. You can avoid most uses of AND() by ordering the tests from low-to-high values, as I did. So the above could be written more concisely as: =if(W44<=0.5, "", if(W44<38.5, "empty", if(W44<39.5, "low", if(W44<40.5, "ok", "full")))) Note that the second test, for example, is implicitly the same as AND(W44>0.5,W44<38.5) because we get there only if W44<=0.5 is not true, ergo W44>0.5 is true. (By the way, for symmetry and consistency, I suspect you want to W44<0.5, not W44<=0.5.) 3. Beware of nesting functions too deeply. Excel has a nesting limit of 7. You have a nesting level of 4; my approach in #2 has one less nesting level. That is why the use of LOOKUP() is appealing in you case. By the way, my previous use of LOOKUP() was unnecessarily complicated. (I started with one idea, ended up with another, but failed to make simplifying adjustments.) In you latest case, you could write: =if(W44<=0.5, "", lookup(W44, {0.5,38.5,39.5,40.5}, {"empty", "low", "ok", "full"})) LOOKUP() matches the largest value less than or equal to W44. I wrote: > =if( A1<38, "empty", lookup(A1-38, {0,1,2,3}, {"quarter","half","three > quarters","full"}) ) Obviously that can be simplified. I started with one idea, ended up with another, but failed to make simplifying adjustments. The above can be written more simply and it is easier to understand as: =if(A1<38, "empty", lookup(A1, {38,39,40,41}, {"quarter","half","three quarters","full"})) LOOKUP() matches the largest value less than or equal to A1. For simplicity: =LOOKUP(A1,{0,38,39,40,41;"Empty","One Quarter","Half","Three Quarters","Full"}) SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > I wrote: > > =if( A1<38, "empty", lookup(A1-38, {0,1,2,3}, {"quarter","half","three [quoted text clipped - 8 lines] > > LOOKUP() matches the largest value less than or equal to A1. > For simplicity: > =LOOKUP(A1,{0,38,39,40,41;"Empty","One Quarter","Half","Three > Quarters","Full"}) That presumes a constraint on A1 that the OP did not state, namely that A1 will not be less than zero. The OP said only that A1 can be less than 38; that does not preclude negative values. If A1 were negative, your formulation would return #N/A. I consider that unaccecptable. Good point. Care to comment on: =LOOKUP(A1,{-9E+307,38,39,40,41;"Empty","One Quarter","Half","Three Quarters","Full"}) ? SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > > For simplicity: > > =LOOKUP(A1,{0,38,39,40,41;"Empty","One Quarter","Half","Three [quoted text clipped - 4 lines] > than 38; that does not preclude negative values. If A1 were negative, > your formulation would return #N/A. I consider that unaccecptable. > Care to comment on: > =LOOKUP(A1,{-9E+307,38,39,40,41;"Empty","One Quarter","Half","Three > Quarters","Full"}) Sure: try putting -9.9e307 into A1. Klunk! Eventually, you will hit upon the smallest possible negative number -- at least, what you think is the smallest. But what happens to the correctness of the spreadsheet when the world moves on to 128-bit floating point as the "norm"? (The 80-bit and 128-bit formats are already defined by the standard.) (Hmm, I wonder how many spreadsheets and computer programs will fail then anyway.) I prefer to write formulas that always work -- unless there is good reason for a short-cut. IMHO, if(cond,"",lookup(...)) is not so complex that it deserves a short-cut that does not always work. OTOH, if the OP states that A1 will never be less than zero, I would agree that your original formuation is cleaner. The truth is: I had intended to include it in my previous postings, with the proper constraint duly noted, but I forgot. After I pushed "submit", I did not think it was worth an addendum. I think your original posting would have been just fine if you had explicitly stated your assumptions (aka ass-u-me-tions ;->) for the benefit of the hapless reader. <<<"Sure: try putting -9.9e307 into A1. Klunk!">>> That's *exactly* the point! It lends to a "concise" formulation where that size negative will *never* be met (we're not dealing in abstracts here), and therefore will return a *correct* result for all intents and purposes fulfilling the OP's stipulations. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- . > > Care to comment on: > > =LOOKUP(A1,{-9E+307,38,39,40,41;"Empty","One Quarter","Half","Three [quoted text clipped - 24 lines] > explicitly stated your assumptions (aka ass-u-me-tions ;->) for the > benefit of the hapless reader. joeu2004@hotmail.com wrote... >>Care to comment on: >>=LOOKUP(A1,{-9E+307,38,39,40,41;"Empty","One Quarter","Half","Three [quoted text clipped - 7 lines] >floating point as the "norm"? (The 80-bit and 128-bit formats are >already defined by the standard.) ... >I prefer to write formulas that always work -- unless there is good >reason for a short-cut. IMHO, if(cond,"",lookup(...)) is not so >complex that it deserves a short-cut that does not always work. ... Too many darn pedantic trees in the way to see the forest? You want fairly simple, general, and FTHOI concise, =CHOOSE(MIN(MAX(A1,37)-36,5),"Empty","One Quarter","Half","Three Quarters","Full") You want it simpler at the cost of a bit less concise, =LOOKUP(MAX(A1,37),{37,"Empty";38,"One Quarter";39,"Half";40,"Three Quarters"; 41,"Full"}) As long as your theoretical 1024-bit CPU can handle integers in the range 37 to 41, this should work. > You want it simpler at the cost of a bit less concise, > =LOOKUP(MAX(A1,37),{37,"Empty";38,"One Quarter";39,"Half";40,"Three > Quarters"; 41,"Full"}) I agree: this is the best. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.