To many IF's for xl2003

[BradleyS]

I have been informed that xl2003 can only use up to 7 IF statements and if I have to use more than this then there is probably a better way to create the formula.

I of course have no idea how to improve on the formula otherwise I wouldn't need to post it. So here is the formula I can use in xl2010, but can't in xl2003:

Excel 2010
	D
2	FLU1
Sheet1

Cell Formulas
Range	Formula
D2	=IF(DATE(1938,9,1)>=$B2,"FLU2",IF(DATE(1948,9,1)>=$B2,"FLU1",IF(DATE(1948,9,1)<$B2,IF(L2="Carer","ZFCARE",IF(L2="Chronic Heart Disease","ZFCHD",IF(L2="Chronic Liver Disease","ZFCLD",IF(L2="Chronic Neurological Disease","ZFCNS",IF(L2="Chronic Renal Disease","ZFCRD",IF(L2="Chronic Respiratory Disease","ZFCRES",IF(L2="Diabetes","ZFDM",IF(L2="Long-stay residential","ZFHOME",IF(L2="Immunosuppression","ZFIMM",IF(L2="Pregnant","ZFPREG",IF(L2="Stroke / TIA","ZFSTIA",IF(AND(B2<=DATE(2010,9,1),B2>DATE(2009,9,1)),"ZLF3",IF(AND(B2<=DATE(2011,9,1),B2>DATE(2010,9,1)),"ZFL2",IF(L2="","Unidentified",)))))))))))))),"N/A")))

Here is the table of data I need to find the correct code for in cell D. My above formula doesn't include Pneumcoccal as I was busy trying to get the FLU to work, but Pneumo will have to use the text in IMMTYPE column J to workout the code

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	DOB	01/09/2013	Code						IMMSTYPE	STAGE	REASONTXT
2		20/02/1944	69y 6m 12d	FLU1						FLU
3		02/11/1910	102y 9m 30d	FLU2						FLU
4		21/09/1942	70y 11m 11d	PNEU						PNEUMOPOLY
5		12/11/1970	42y 9m 20d	ZFDM						FLU		Diabetes
6		03/08/1963	50y 0m 29d	ZFCRES						FLU		Chronic Respiratory Disease
7		03/11/1962	50y 9m 29d	ZFSTIA						FLU		Stroke / Tia
8		17/03/1963	50y 5m 15d	ZFDM						FLU		Diabetes
9		15/01/1967	46y 7m 17d	ZFCRD						FLU		Chronic Renal Disease
10		20/10/1969	43y 10m 12d	ZFCHD						FLU		Chronic Heart Disease
11		05/10/1963	49y 10m 27d	ZFCNS						FLU		Chronic Neurological Disease
12		13/04/1960	53y 4m 19d	ZFCRES						FLU		Chronic Respiratory Disease
13		09/12/1969	43y 8m 23d	ZFSTIA						FLU		Stroke / Tia
14		28/10/1969	43y 10m 4d	Unidentified						FLU
15		01/09/2011	2y 0m 0d	ZFL2						FLUSIN	1
16		01/09/2010	3y 0m 0d	ZLF3						FLU
17		02/01/2005	8y 7m 30d	ZFCRES						FLUSIN	1	Chronic Respiratory Disease
18		08/01/2005	8y 7m 24d	ZFCRD						FLUSIN	1	Chronic Renal Disease
19		01/08/2011	2y 1m 0d	PNCHMG						PNEUMOCONJ13
Sheet1

Here are the rules that make up the code in column D

Excel 2003
	A	B
1	Code	Description
2	FLU1	INFLUENZA VACC AGE 65 - 74
3	FLU2	INFLUENZA VACC AGED 75 AND OVER
4	ZFCARE	FLU VACC <65 AT RISK MAIN CARER
5	ZFCHD	FLU VACC <65 AT RISK CHRONIC HEART DISEASE
6	ZFCLD	FLU VACC <65 AT RISK CHRONIC LIVER DISEASE
7	ZFCNS	FLU VACC <65 AT RISK NEUROLOGICAL DISEASE
8	ZFCRD	FLU VACC <65 AT RISK CHRONIC RENAL DISEASE
9	ZFCRES	FLU VACC <65 AT RISK RESPIRATORY DISEASE
10	ZFDM	FLU VACC <65 AT RISK DIABETES MELLITUS
11	ZFHOME	FLU VACC <65 AT RISK CARE HOME RESIDENT
12	ZFIMM	FLU VACC <65 AT RISK IMMUNOSUPPRESSED
13	ZFPREG	FLU VACC <65 AT RISK PREGNANT WOMEN
14	ZFSTIA	FLU VACC <65 AT RISK STROKE TIA
15	PNEU	PNEUMOCOCCAL INJECTION AGE 65 AND OVER
16	PNCHMG	CHILD PNEUMO VACC FINAL DOSE UNDER 5
17	ZFL2	INFLUENZA 2 YEAR OLDS
18	ZFL3	INFLUENZA 3 YEAR OLDS
Sheet2

Any help in this would be most appreciated as I have hundreds to check through

 

[shg]

Just to repeat, my table does not change your existing table. It's a DIFFERENT table that enables the use of a simple formula.

I'll leave you in Mark's hands.

 

[markmzz]

Just to repeat, my table does not change your existing table. It's a DIFFERENT table that enables the use of a simple formula.

I'll leave you in Mark's hands.

I agree with Shg, but if you want a formula without table, here you have another (a big formula too):

Use only Enter to enter the formula

=IF(DATEDIF(B2,$C$1,"y")>=75,"FLU2",IF(DATEDIF(B2,$C$1,"y")>=65,"FLU1",IF(L2<>"",
VLOOKUP($L2,{"Carer","ZFCARE";"Chronic Heart Disease","ZFCHD";"Chronic Liver Disease","ZFCLD";"Chronic Neurological Disease","ZFCNS";"Chronic Renal Disease",
"ZFCRD";"Chronic Respiratory Disease","ZFCRES";"Diabetes","ZFDM";"Long-stay residential","ZFHOME";"Immunosuppression","ZFIMM";"Pregnant","ZFPREG";"Stroke / TIA","ZFSTIA"},2,0),
IF(DATEDIF(B2,$C$1,"y")=3,"ZFL3",IF(DATEDIF(B2,$C$1,"y")=2,"ZFL2",IF(J2="PNEUMOCOCONJ13","PNCHMG",IF(J2="PNEUMOPOLY","PNEU","UNIDENTIFIED")))))))

Markmzz

 

[markmzz]

Here is my last formula with the table of the post#5:

=IF(DATEDIF(B2,$C$1,"y")>=75,"FLU2",IF(DATEDIF(B2,$C$1,"y")>=65,"FLU1",IF(L2<>"",
VLOOKUP($L2,$N$2:$O$12,2,0),
IF(DATEDIF(B2,$C$1,"y")=3,"ZFL3",
IF(DATEDIF(B2,$C$1,"y")=2,"ZFL2",
IF(J2="PNEUMOCOCONJ13","PNCHMG",IF(J2="PNEUMOPOLY","PNEU","UNIDENTIFIED")))))))

PS: you can put that table in another sheet.

Markmzz

 

[snb_]

Less if's

=CHOOSE(MATCH(YEAR(C1)-YEAR(B2);{0;2;3;65;75;100};1);IF(L2="";"";CHOOSE(MATCH($L2;{"Carer";"Chronic Heart Disease";"Chronic Liver Disease";"Chronic Neurological Disease";"Chronic Renal Disease";"Chronic Respiratory Disease";"Diabetes";"Long-stay residential";"Immunosuppression";"Pregnant";"Stroke / TIA"};0);"ZFCARE";"ZFCHD";"ZFCLD";"ZFCNS";"ZFCRD";"ZFCRES";"ZFDM";"ZFHOME";"ZFIMM";"ZFPREG";"ZFSTIA")) & IF(J2="PNEUMOCOCONJ13";"PNCHMG";IF(J2="PNEUMOPOLY";"PNEU";"UNIDENTIFIED"));"ZLF2";"ZLF3";"FLU1";"FLU2")

 

[markmzz]

Another small modification:

=IF(DATEDIF(B2,$C$1,"y")>=75,"FLU2",IF(DATEDIF(B2,$C$1,"y")>=65,"FLU1",IF(L2<>"",
VLOOKUP($L2,$N$2:$O$12,2,0),
IF(OR(DATEDIF(B2,$C$1,"y")={2;3}),"ZFL"&DATEDIF(B2,$C$1,"y"),
IF(J2="PNEUMOCOCONJ13","PNCHMG",IF(J2="PNEUMOPOLY","PNEU","UNIDENTIFIED"))))))

Markmzz

 

[markmzz]

A small modification:

With table

=IF(DATEDIF(B2,$C$1,"y")>=75,"FLU2",IF(DATEDIF(B2,$C$1,"y")>=65,"FLU1",IF(L2="",
IF(OR(DATEDIF(B2,$C$1,"y")={2;3}),"ZFL"&DATEDIF(B2,$C$1,"y"),
IF(J2="PNEUMOCOCONJ13","PNCHMG",IF(J2="PNEUMOPOLY","PNEU","UNIDENTIFIED"))),
VLOOKUP($L2,$N$2:$O$12,2,0))))

Without table

=IF(DATEDIF(B2,$C$1,"y")>=75,"FLU2",IF(DATEDIF(B2,$C$1,"y")>=65,"FLU1",IF(L2="",
IF(OR(DATEDIF(B2,$C$1,"y")={2;3}),"ZFL"&DATEDIF(B2,$C$1,"y"),
IF(J2="PNEUMOCOCONJ13","PNCHMG",IF(J2="PNEUMOPOLY","PNEU","UNIDENTIFIED"))),
VLOOKUP($L2,{"Carer","ZFCARE";"Chronic Heart Disease","ZFCHD";"Chronic Liver Disease",
"ZFCLD";"Chronic Neurological Disease","ZFCNS";"Chronic Renal Disease","ZFCRD";"Chronic Respiratory Disease",
"ZFCRES";"Diabetes","ZFDM";"Long-stay residential","ZFHOME";"Immunosuppression","ZFIMM";"Pregnant","ZFPREG";"Stroke / TIA","ZFSTIA"},2,0))))

Markmzz

 

[BradleyS]

OMG! This is what dreams are made of

I sort of understand your formula (although I would never have been able to accomplish it. It's one thing reading it and it’s an entirely different ball game writing it)

Thank you ever so much for this, it will save me so much time.

I'm continually amazed what can be achieved in XL.

Thank you, Thank You, Thank, You.......

 

[markmzz]

OMG! This is what dreams are made of

I sort of understand your formula (although I would never have been able to accomplish it. It's one thing reading it and it’s an entirely different ball game writing it)

Thank you ever so much for this, it will save me so much time.

I'm continually amazed what can be achieved in XL.

Thank you, Thank You, Thank, You.......

You're welcome.

And I'm glad that your problem was solved and thank you for the feedback.

Markmzz

 

[BradleyS]

I'm probably pushing my luck here, but I'm so happy that it works and i will be using it a lot, so i thought I would stick in in a macro, but it keeps stopping with an error message:

Runtime error 1004
Unable to set the FormulaArray property of the range class

...here is the VBA code of the formula

    Range("D2").Select
    Selection.FormulaArray = _
        "=IF(RC2<=1*""1/9/1938"",""FLU2"",IF(RC2<=1*""1/9/1948"",""FLU1"",IF(OR(RC12={""Carer"";""Chronic Heart Disease"";""Chronic Liver Disease"";""Chronic Neurological Disease"";""Chronic Renal Disease"";""Chronic Respiratory Disease"";""Diabetes"";""Long-stay residential"";""Immunosuppression"";""Pregnant"";""Stroke / TIA""}),VLOOKUP(RC12,{""Carer"",""ZFCARE"";""Chronic " & _
        "Heart Disease"",""ZFCHD"";""Chronic Liver Disease"",""ZFCLD"";""Chronic Neurological Disease"",""ZFCNS"";""Chronic Renal Disease"",""ZFCRD"";""Chronic Respiratory Disease"",""ZFCRES"";""Diabetes"",""ZFDM"";""Long-stay residential"",""ZFHOME"";""Immunosuppression"",""ZFIMM"";""Pregnant"",""ZFPREG"";""Stroke / TIA"",""ZFSTIA""},2,0),IF(OR(RC10={""PNEUMOPOLY"";""PNEUMOCONJ13""" & _
        "}),VLOOKUP(RC10,{""PNEUMOPOLY"",""PNEU"";""PNEUMOCONJ13"",""PNCHMG""},2,0),IF(AND(RC2<=1*""1/9/2010"",RC2>1*""1/9/2009""),""ZLF3"",IF(AND(RC2<=1*""1/9/2011"",RC2>1*""1/9/2010""),""ZFL2"",IF(RC12="""",""Unidentified"",""N/A"")))))))"

...and here is the final formula I am using:

Excel 2010
	D
2	FLU2
Upload Data

Cell Formulas
Range	Formula
D2	{=IF($B2<=1*"1/9/1938","FLU2",IF($B2<=1*"1/9/1948","FLU1",IF(OR($L2={"Carer";"Chronic Heart Disease";"Chronic Liver Disease";"Chronic Neurological Disease";"Chronic Renal Disease";"Chronic Respiratory Disease";"Diabetes";"Long-stay residential";"Immunosuppression";"Pregnant";"Stroke / TIA"}),VLOOKUP($L2,{"Carer","ZFCARE";"Chronic Heart Disease","ZFCHD";"Chronic Liver Disease","ZFCLD";"Chronic Neurological Disease","ZFCNS";"Chronic Renal Disease","ZFCRD";"Chronic Respiratory Disease","ZFCRES";"Diabetes","ZFDM";"Long-stay residential","ZFHOME";"Immunosuppression","ZFIMM";"Pregnant","ZFPREG";"Stroke / TIA","ZFSTIA"},2,0),IF(OR($J2={"PNEUMOPOLY";"PNEUMOCONJ13"}),VLOOKUP($J2,{"PNEUMOPOLY","PNEU";"PNEUMOCONJ13","PNCHMG"},2,0),IF(AND($B2<=1*"1/9/2010",$B2>1*"1/9/2009"),"ZLF3",IF(AND($B2<=1*"1/9/2011",$B2>1*"1/9/2010"),"ZFL2",IF($L2="","Unidentified","N/A")))))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

The macro doesn't seem to record the formula properly either, because when it creates a line break it looses bits of the formula, but I think I have resolved this with the code shown above.

I just keep getting the error message now.

 

[markmzz]

I'm probably pushing my luck here, but I'm so happy that it works and i will be using it a lot, so i thought I would stick in in a macro, but it keeps stopping with an error message:

Runtime error 1004
Unable to set the FormulaArray property of the range class

...here is the VBA code of the formula

    Range("D2").Select
    Selection.FormulaArray = _
        "=IF(RC2<=1*""1/9/1938"",""FLU2"",IF(RC2<=1*""1/9/1948"",""FLU1"",IF(OR(RC12={""Carer"";""Chronic Heart Disease"";""Chronic Liver Disease"";""Chronic Neurological Disease"";""Chronic Renal Disease"";""Chronic Respiratory Disease"";""Diabetes"";""Long-stay residential"";""Immunosuppression"";""Pregnant"";""Stroke / TIA""}),VLOOKUP(RC12,{""Carer"",""ZFCARE"";""Chronic " & _
        "Heart Disease"",""ZFCHD"";""Chronic Liver Disease"",""ZFCLD"";""Chronic Neurological Disease"",""ZFCNS"";""Chronic Renal Disease"",""ZFCRD"";""Chronic Respiratory Disease"",""ZFCRES"";""Diabetes"",""ZFDM"";""Long-stay residential"",""ZFHOME"";""Immunosuppression"",""ZFIMM"";""Pregnant"",""ZFPREG"";""Stroke / TIA"",""ZFSTIA""},2,0),IF(OR(RC10={""PNEUMOPOLY"";""PNEUMOCONJ13""" & _
        "}),VLOOKUP(RC10,{""PNEUMOPOLY"",""PNEU"";""PNEUMOCONJ13"",""PNCHMG""},2,0),IF(AND(RC2<=1*""1/9/2010"",RC2>1*""1/9/2009""),""ZLF3"",IF(AND(RC2<=1*""1/9/2011"",RC2>1*""1/9/2010""),""ZFL2"",IF(RC12="""",""Unidentified"",""N/A"")))))))"

The macro doesn't seem to record the formula properly either, because when it creates a line break it looses bits of the formula, but I think I have resolved this with the code shown above.

I just keep getting the error message now.

Look at this link:

Run-time error using FormulaArray property

The formula have more than 255 characters (>800).

Markmzz