Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
ditchy
 
Posts: n/a
Default if greater or lesser than

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(H20040,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

  #2   Report Post  
Bryan Hessey
 
Posts: n/a
Default


for your single test, try

=IF(AND(A139,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")))))))



ditchy 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(H20040,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
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=392404

  #3   Report Post  
Max
 
Posts: n/a
Default

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 <atyahoo<dotcom
----
"ditchy" wrote in message
oups.com...
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(H20040,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



  #4   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On 2 Aug 2005 20:54:00 -0700, "ditchy" 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(H20040,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
  #5   Report Post  
ditchy
 
Posts: n/a
Default

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



  #6   Report Post  
Max
 
Posts: n/a
Default

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 <atyahoo<dotcom
----
"ditchy" wrote in message
oups.com...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to show data greater than 10 in pivot table Angus Excel Discussion (Misc queries) 6 September 15th 05 07:51 PM
if greater then 99 then 1 if greater then 199 then two N.R. Excel Worksheet Functions 2 June 23rd 05 06:14 PM
Help with SUMIF, INDEX, LOOKUP Please !! Robert Excel Worksheet Functions 13 March 13th 05 01:17 AM
Help w/ SUMIF ; INDEX Robert Excel Worksheet Functions 2 March 12th 05 03:23 AM
Addition to Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 9th 04 03:06 AM


All times are GMT +1. The time now is 12:50 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"