Greater, equal to or less than question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mag()() - 14 Jul 2007 04:11 GMT

I need to write a formula and I am little stuck with it. I have a
requirement to write a formula which has to state the following....

Where "cell a1" is less than 16 then "less" where "cell a1" is greater that
16 BUT less than 32 then "50%" where "cell a1" is equal to or greater than
32 then "OK"

Hope this is clear and hope someone can help me as I have been stuck with
this fora while now.

Plz help

mag()()

Reply to this Message

T. Valko - 14 Jul 2007 04:32 GMT

You've left out 2 possible conditions:

=16 and an empty cell 0

If the cell is empty it will evaluate as 0. 0 is <16 so "Less" would be the
returned value.

You define <16 and >16 but not =16.

Try this with the first condition being <=16:

=IF(ISNUMBER(A1),IF(A1<=16,"Less",IF(A1<32,50%,"OK")),"")

If you want the 50% to be a numeric value then format the cell as
PERCENTAGE. If you want the 50% to be a TEXT value enclose it in quotes in
the formula: "50%".

Using the ISNUMBER test takes care of 2 potential problems. It takes care of
the empty cell situation and it also prevents an incorrect result if a text
entry is accidentally made in the cell.

Signature

Biff
Microsoft Excel MVP

>I need to write a formula and I am little stuck with it. I have a
>requirement to write a formula which has to state the following....
[quoted text clipped - 9 lines]
>
> mag()()

Reply to this Message

Stan Brown - 14 Jul 2007 11:37 GMT

Sat, 14 Jul 2007 04:11:42 +0100 from Mag()()
<mrmagoo_ukNOSPAM@hotmail.com>:

> I need to write a formula and I am little stuck with it. I have a
> requirement to write a formula which has to state the following....
>
> Where "cell a1" is less than 16 then "less" where "cell a1" is greater that
> 16 BUT less than 32 then "50%" where "cell a1" is equal to or greater than
> 32 then "OK"

Write it pretty much how you said it:

=if(A1<16,"less",if(A1<32,"50%","greater"))

You left out the =16 case, by the way. I lumped it in with 16-32.

Signature

Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Reply to this Message