if greater or lesser than

D

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
 
B

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

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

Ron Rosenfeld

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
Click to expand...

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
 
D

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
 
M

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