Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Hello Programmers! I have a formula that checks to see if a certain value is in between two numbers: =IF(W4435,IF(W44<37.5,\"EMPTY\")) Is there another way to (more simple) to write this formula. Thanks, EMoe -- EMoe ------------------------------------------------------------------------ EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183 View this thread: http://www.excelforum.com/showthread...hreadid=566352 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
EMoe wrote:
I have a formula that checks to see if a certain value is in between two numbers: =IF(W4435,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 W4435 instead of 35<W44. I write it that way because it more closely mimics what we write mathematically, e.g. 35 < W44 < 37.5. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
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 -- EMoe ------------------------------------------------------------------------ EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183 View this thread: http://www.excelforum.com/showthread...hreadid=566352 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Did you read what you posted?
What do you want to happen if A1 *exactly* matches 38, 39, 40, and 41? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "EMoe" wrote in message ... 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 -- EMoe ------------------------------------------------------------------------ EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183 View this thread: http://www.excelforum.com/showthread...hreadid=566352 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
EMoe wrote:
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"}) ) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
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(W440.5,W44<38.5),"Empty",IF(AND(W4438.5, W44<39.5),"Low",IF(AND(W4439.5,W44<40.5),"Ok",IF( W4440.5,"Full",IF(W44<0.5,""))))) Thanks Again! EMoe -- EMoe ------------------------------------------------------------------------ EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183 View this thread: http://www.excelforum.com/showthread...hreadid=566352 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
What happens if W44 = 38.5? -ep -- edwardpestian ------------------------------------------------------------------------ edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809 View this thread: http://www.excelforum.com/showthread...hreadid=566352 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
EMoe wrote:
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(W440.5,W44<38.5),"Empty",IF(AND(W4438.5, W44<39.5),"Low",IF(AND(W4439.5,W44<40.5),"Ok",IF( W4440.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(W440.5,W44<38.5) because we get there only if W44<=0.5 is not true, ergo W440.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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
For simplicity:
=LOOKUP(A1,{0,38,39,40,41;"Empty","One Quarter","Half","Three Quarters","Full"}) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message ps.com... 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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Ragdyer wrote:
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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Good point.
Care to comment on: =LOOKUP(A1,{-9E+307,38,39,40,41;"Empty","One Quarter","Half","Three Quarters","Full"}) ? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- wrote in message oups.com... Ragdyer wrote: 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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Ragdyer wrote:
Care to comment on: =LOOKUP(A1,{-9E+307,38,39,40,41;"Empty","One Quarter","Half","Three Quarters","Full"}) Su 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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
<<<"Su 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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- .. wrote in message oups.com... Ragdyer wrote: Care to comment on: =LOOKUP(A1,{-9E+307,38,39,40,41;"Empty","One Quarter","Half","Three Quarters","Full"}) Su 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. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Harlan Grove wrote:
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel displaying formulae as constant and not calculating formula | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
How can i get an If formula in excel to edit another cell? | Excel Worksheet Functions | |||
converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
Formula Integrity Not Preserved During Sort in Excel 2000 | Excel Discussion (Misc queries) |