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

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.

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

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

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"}) )

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

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

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.

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"})

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