| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF and AND statement
Hi,
Please can you let me know why this formula works: =IF(AND(A10,A1<99),"First",IF(AND(A198,A1<183)," Second",IF(AND(A1182,A1<267),"Third",IF(AND(A126 6,A1<351),"Fourth",IF(AND(A1350,A1<435),"Five",IF (AND(A1434,A1<519),"Sixth",IF(AND(A1518,A1<603), "Seventh","Older"))))))) and this formula does not work & and how do I fix it: =IF(AND(A10,A1<99),"First",IF(AND(A198,A1<183)," Second",IF(AND(A1182,A1<267),"Third",IF(AND(A126 6,A1<351),"Fourth",IF(AND(A1350,A1<435),"Five",IF (AND(A1434,A1<519),"Sixth",IF(AND(A1518,A1<603), "Seventh",IF(AND(A1602,A1<687),"Eight","Older"))) )))) As soon as I add the eight on, excel has a problem Regards, |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF and AND statement
An IF formula can only handle 7 if's.
You will have to change your tactics here, and either se something like VLOOKUP, an array, or a second column, if you want to go beyond 7 IF's -- HTH Kassie Replace xxx with hotmail "Ward" wrote: Hi, Please can you let me know why this formula works: =IF(AND(A10,A1<99),"First",IF(AND(A198,A1<183)," Second",IF(AND(A1182,A1<267),"Third",IF(AND(A126 6,A1<351),"Fourth",IF(AND(A1350,A1<435),"Five",IF (AND(A1434,A1<519),"Sixth",IF(AND(A1518,A1<603), "Seventh","Older"))))))) and this formula does not work & and how do I fix it: =IF(AND(A10,A1<99),"First",IF(AND(A198,A1<183)," Second",IF(AND(A1182,A1<267),"Third",IF(AND(A126 6,A1<351),"Fourth",IF(AND(A1350,A1<435),"Five",IF (AND(A1434,A1<519),"Sixth",IF(AND(A1518,A1<603), "Seventh",IF(AND(A1602,A1<687),"Eight","Older"))) )))) As soon as I add the eight on, excel has a problem Regards, |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF and AND statement
The formula is in column B and in column A is a list of numbers ranging from
1-1000 "Ward" wrote: Hi, Please can you let me know why this formula works: =IF(AND(A10,A1<99),"First",IF(AND(A198,A1<183)," Second",IF(AND(A1182,A1<267),"Third",IF(AND(A126 6,A1<351),"Fourth",IF(AND(A1350,A1<435),"Five",IF (AND(A1434,A1<519),"Sixth",IF(AND(A1518,A1<603), "Seventh","Older"))))))) and this formula does not work & and how do I fix it: =IF(AND(A10,A1<99),"First",IF(AND(A198,A1<183)," Second",IF(AND(A1182,A1<267),"Third",IF(AND(A126 6,A1<351),"Fourth",IF(AND(A1350,A1<435),"Five",IF (AND(A1434,A1<519),"Sixth",IF(AND(A1518,A1<603), "Seventh",IF(AND(A1602,A1<687),"Eight","Older"))) )))) As soon as I add the eight on, excel has a problem Regards, |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF and AND statement
oh dear, I'm going to have to think again
think I'm going to have a second column for the next set of 'if' statements, its probably the easiest way 'if' only excel could do more than 7 ifs "Kassie" wrote: An IF formula can only handle 7 if's. You will have to change your tactics here, and either se something like VLOOKUP, an array, or a second column, if you want to go beyond 7 IF's -- HTH Kassie Replace xxx with hotmail "Ward" wrote: Hi, Please can you let me know why this formula works: =IF(AND(A10,A1<99),"First",IF(AND(A198,A1<183)," Second",IF(AND(A1182,A1<267),"Third",IF(AND(A126 6,A1<351),"Fourth",IF(AND(A1350,A1<435),"Five",IF (AND(A1434,A1<519),"Sixth",IF(AND(A1518,A1<603), "Seventh","Older"))))))) and this formula does not work & and how do I fix it: =IF(AND(A10,A1<99),"First",IF(AND(A198,A1<183)," Second",IF(AND(A1182,A1<267),"Third",IF(AND(A126 6,A1<351),"Fourth",IF(AND(A1350,A1<435),"Five",IF (AND(A1434,A1<519),"Sixth",IF(AND(A1518,A1<603), "Seventh",IF(AND(A1602,A1<687),"Eight","Older"))) )))) As soon as I add the eight on, excel has a problem Regards, |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF and AND statement
Excel 2007 can.
-- David Biddulph "Ward" wrote in message ... .... 'if' only excel could do more than 7 ifs .... |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF and AND statement
Try this:
=IF(A1="","",LOOKUP(A1,{1,99,183,267,351,435,519,6 03,687},{"First","Second","Third","Fourth","Five", "Sixth","Seventh","Eight","Older"})) "Ward" wrote: Hi, Please can you let me know why this formula works: =IF(AND(A10,A1<99),"First",IF(AND(A198,A1<183)," Second",IF(AND(A1182,A1<267),"Third",IF(AND(A126 6,A1<351),"Fourth",IF(AND(A1350,A1<435),"Five",IF (AND(A1434,A1<519),"Sixth",IF(AND(A1518,A1<603), "Seventh","Older"))))))) and this formula does not work & and how do I fix it: =IF(AND(A10,A1<99),"First",IF(AND(A198,A1<183)," Second",IF(AND(A1182,A1<267),"Third",IF(AND(A126 6,A1<351),"Fourth",IF(AND(A1350,A1<435),"Five",IF (AND(A1434,A1<519),"Sixth",IF(AND(A1518,A1<603), "Seventh",IF(AND(A1602,A1<687),"Eight","Older"))) )))) As soon as I add the eight on, excel has a problem Regards, |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF and AND statement
Hi,
Can you help make this formula shorter/more efficient - I need to go up to 90th =IF(A1="","",LOOKUP(A1,{1,99,183,267,351,435,519,6 03,687,771,855,939,1023,1107,1191,1275,1359,1443,1 527,1611,1695,1779,1863,1947,2031,2115,2199,2283,2 367,2451,2535,2619,2703,2787,2871,2955,3039,3123,3 207,3291,3375,3459,3543,3627,3711,3795,3879,3963,4 047,4131,4215,4299,4383,4467,4551,4635,4719,4803,4 887,4971,5055,5139,5223,5307,5391,5475,5559,5643,5 727,5811,5895,5979,6063,6147,6231,6315,6399,6483,6 567,6651,6735,6819,6903,6987},{"1st","2nd","3rd"," 4th","5th","6th","7th","8th","9th","10th","11th"," 12th","13th","14th","15th","16th","17th","18th","1 9th","20","21st","22nd","23rd","24th","25th","26th ","27th","28th","29th","30th","31st","32nd","33rd" ,"34th","35th","36th","37th","38th","39th","40th", "41st","42nd","43rd","44th","45th","46th","47th"," 48th","49th","50th","51st","52nd","53rd","54th","5 5th","56th","57th","58th","59th","60th","61st","62 nd","63rd","64th","65th","66th","67th","68th","69t h","70th","71st","72nd","73rd","74th","75th","76th ","77th","78th","79th","Older"})) "Teethless mama" wrote: Try this: =IF(A1="","",LOOKUP(A1,{1,99,183,267,351,435,519,6 03,687},{"First","Second","Third","Fourth","Five", "Sixth","Seventh","Eight","Older"})) "Ward" wrote: Hi, Please can you let me know why this formula works: =IF(AND(A10,A1<99),"First",IF(AND(A198,A1<183)," Second",IF(AND(A1182,A1<267),"Third",IF(AND(A126 6,A1<351),"Fourth",IF(AND(A1350,A1<435),"Five",IF (AND(A1434,A1<519),"Sixth",IF(AND(A1518,A1<603), "Seventh","Older"))))))) and this formula does not work & and how do I fix it: =IF(AND(A10,A1<99),"First",IF(AND(A198,A1<183)," Second",IF(AND(A1182,A1<267),"Third",IF(AND(A126 6,A1<351),"Fourth",IF(AND(A1350,A1<435),"Five",IF (AND(A1434,A1<519),"Sixth",IF(AND(A1518,A1<603), "Seventh",IF(AND(A1602,A1<687),"Eight","Older"))) )))) As soon as I add the eight on, excel has a problem Regards, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
| If statement or lookup statement not sure | Excel Worksheet Functions | |||
| SUMIF statement with AND statement | Excel Discussion (Misc queries) | |||
| appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
| If statement and Isblank statement | Excel Worksheet Functions | |||
Linear Mode
