if greater or lesser than

[ditchy]

Hello, can someone give me some help with this please
H200 is age
I need to add a new age group which is 40+
I have tried to add this to the formula but have trouble getting it to
work =if(H200>40,H200<50,"40+"

=IF(H200>=50,"vet",IF(H200>=20,"open",IF(H200>=18,"under
20",IF(H200>=16,"under 18",IF(H200>=14,"under 16",IF(H200<14,"under
14","false"))))))

all help much appreciated
regards
Ditchy

[Bryan Hessey]

for your single test, try

=IF(AND(A1>39,A1<50),"40+","not")

or add it into your existing formula
=IF(H200>=50,"vet",IF(H200>=40,"40+",IF(H200>=20,"open",IF(H200>=18,"under 20",IF(H200>=16,"under 18",IF(H200>=14,"under 16",IF(H200<14,"under 14","false")))))))

Hello, can someone give me some help with this please
H200 is age
I need to add a new age group which is 40+
I have tried to add this to the formula but have trouble getting it to
work =if(H200>40,H200<50,"40+"

=IF(H200>=50,"vet",IF(H200>=20,"open",IF(H200>=18,"under
20",IF(H200>=16,"under 18",IF(H200>=14,"under 16",IF(H200<14,"under
14","false"))))))

all help much appreciated
regards
Ditchy

[Max]

One way, try:

=IF(H200="","",VLOOKUP(H200,{0,"under 14";14,"under 16";16,"under
18";18,"under 20";20,"open";40,"40+";50,"vet"},2))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ditchy" <[email protected]> wrote in message
news:[email protected]...
> Hello, can someone give me some help with this please
> H200 is age
> I need to add a new age group which is 40+
> I have tried to add this to the formula but have trouble getting it to
> work =if(H200>40,H200<50,"40+"
>
> =IF(H200>=50,"vet",IF(H200>=20,"open",IF(H200>=18,"under
> 20",IF(H200>=16,"under 18",IF(H200>=14,"under 16",IF(H200<14,"under
> 14","false"))))))
>
> all help much appreciated
> regards
> Ditchy
>

[Ron Rosenfeld]

On 2 Aug 2005 20:54:00 -0700, "ditchy" <[email protected]> wrote:

>Hello, can someone give me some help with this please
>H200 is age
>I need to add a new age group which is 40+
>I have tried to add this to the formula but have trouble getting it to
>work =if(H200>40,H200<50,"40+"
>
>=IF(H200>=50,"vet",IF(H200>=20,"open",IF(H200>=18,"under
>20",IF(H200>=16,"under 18",IF(H200>=14,"under 16",IF(H200<14,"under
>14","false"))))))
>
>all help much appreciated
>regards
>Ditchy

Although you can do this with nested IF's; it may be simpler to use VLOOKUP,
especially if you wish to add another condition.

Set up a table someplace on your worksheet as follows:

0 under 14
14 under 16
16 under 18
18 under 20
20 open
40 40+
50 vet


You can use a cell range reference (e.g. J1:K7) or Name it "tbl".

Then use the formula:

=IF(H200="","false",VLOOKUP(H200,tbl,2))

A couple of caveats:

1. If you really want H200 = 40 to result in "open", change the 40 in column
one of the table to something like 40.001 (or whatever the smallest possible
increment of H200 might be).

2. The above formula will give #N/A if H200 is not a number equal to or
greater than zero. This can be changed if necessary.


--ron

[ditchy]

Thank You Bryan and all the others that have responded. I have used
your suggestion Bryan and it has worked a treat, much appreciated
Ditchy

[Max]

Thanks for the feedback, Ditchy
(the one from Ballarat? .. from a few years back <g>)

Do go as preferred / with what you are comfortable with. But do hold a
thought or 2 in reserve for the VLOOKUP option illustrated (especially the
one outlined in Ron's response), which would prove an appropriate switch to
make in this instance, if you're going to add on even more conditions in
future. It's much easier to maintain the reference table for the VLOOKUP,
and you won't be bound by the 7 nested limits of IF either (you're already
at six levels there <g>).
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"ditchy" <[email protected]> wrote in message
news:[email protected]...
> Thank You Bryan and all the others that have responded. I have used
> your suggestion Bryan and it has worked a treat, much appreciated
> Ditchy
>