Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to show data greater than 10 in pivot table | Excel Discussion (Misc queries) | |||
if greater then 99 then 1 if greater then 199 then two | Excel Worksheet Functions | |||
Help with SUMIF, INDEX, LOOKUP Please !! | Excel Worksheet Functions | |||
Help w/ SUMIF ; INDEX | Excel Worksheet Functions | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel |