Excel Formula

[EMoe]

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

 

[joeu2004]

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.

 

[EMoe]

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

 

[Ragdyer]

Did you read what you posted?

What do you want to happen if A1 *exactly* matches 38, 39, 40, and 41?

 

[joeu2004]

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

 

[EMoe]

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

 

[edwardpestian]

What happens if W44 = 38.5?

-ep

 

[joeu2004]

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.

 

[joeu2004]

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

 

[Ragdyer]

For simplicity:

=LOOKUP(A1,{0,38,39,40,41;"Empty","One Quarter","Half","Three
Quarters","Full"})

 

[joeu2004]

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.

 

[Ragdyer]

Good point.

Care to comment on:

=LOOKUP(A1,{-9E+307,38,39,40,41;"Empty","One Quarter","Half","Three
Quarters","Full"})
?

 

[joeu2004]

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.

 

[Ragdyer]

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

 

[Harlan Grove]

[email protected] wrote...

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

 

[joeu2004]

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.