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

 

[BradleyS]

these columns go missed off the table:

Excel 2003
	J	K	L
1	IMMSTYPE	STAGE	REASONTXT
2	FLU
3	FLU
4	PNEUMOPOLY
5	FLU		Diabetes
6	FLU		Chronic Respiratory Disease
7	FLU		Stroke / Tia
8	FLU		Diabetes
9	FLU		Chronic Renal Disease
10	FLU		Chronic Heart Disease
11	FLU		Chronic Neurological Disease
12	FLU		Chronic Respiratory Disease
13	FLU		Stroke / Tia
14	FLU
15	FLUSIN	1
16	FLU
17	FLUSIN	1	Chronic Respiratory Disease
18	FLUSIN	1	Chronic Renal Disease
19	PNEUMOCONJ13
Sheet1

 

[shg]

I'm not following all of that, but it seems to me that you could create a table with the relevant DOBs in the top row (probably calculated based on today's date), diagnoses/status down the left column, and the vaccine codes (?) in the body of the table. Then do a 2D lookup.

 

[shg]

Just to flesh that out a little, you could create a table that has all combinations of ReasonTxt and ImmSType:

	A​	B​	C​	D​	E​	F​	G​
1​		As Of Date​	01Sep2013​
2​		Age​	75​	65​	5​	3​	2​
3​	ReasonTxt​	ImmSType​	01Sep1938​	01Sep1948​	01Sep2008​	01Sep2010​	01Sep2011​
4​		FLU	​	​	​	ZFL3​	ZFL2​
5​		FLUSIN	​	​	​	​	​
6​		PNEUMOCONJ13	​	​	​	​	​
7​		PNEUMOPOLY	​	​	​	​	​
8​	Carer	FLU	​	​	​	​	​
9​	Carer	FLUSIN	​	​	​	​	​
10​	Carer	PNEUMOCONJ13	​	​	​	​	​
11​	Carer	PNEUMOPOLY	​	​	​	​	​
12​	Chronic Heart Disease	FLU	​	​	​	​	​
13​	Chronic Heart Disease	FLUSIN	​	​	​	​	​
14​	Chronic Heart Disease	PNEUMOCONJ13	​	​	​	​	​
15​	Chronic Heart Disease	PNEUMOPOLY	​	​	​	​	​
16​	Chronic Neurological Disease	FLU	​	​	​	​	​
17​	Chronic Neurological Disease	FLUSIN	​	​	​	​	​
18​	Chronic Neurological Disease	PNEUMOCONJ13	​	​	​	​	​
19​	Chronic Neurological Disease	PNEUMOPOLY	​	​	​	​	​
20​	Chronic Renal Disease	FLU	​	​	​	​	​
21​	Chronic Renal Disease	FLUSIN	​	​	​	​	​
22​	Chronic Renal Disease	PNEUMOCONJ13	​	​	​	​	​
23​	Chronic Renal Disease	PNEUMOPOLY	​	​	​	​	​
24​	Chronic Respiratory Disease	FLU	​	​	​	​	​
25​	Chronic Respiratory Disease	FLUSIN	​	​	​	​	​
26​	Chronic Respiratory Disease	PNEUMOCONJ13	​	​	​	​	​
27​	Chronic Respiratory Disease	PNEUMOPOLY	​	​	​	​	​
28​	Diabetes	FLU	​	​	​	​	​
29​	Diabetes	FLUSIN	​	​	​	​	​
30​	Diabetes	PNEUMOCONJ13	​	​	​	​	​
31​	Diabetes	PNEUMOPOLY	​	​	​	​	​
32​	Pregnant	FLU	​	​	​	​	​
33​	Pregnant	FLUSIN	​	​	​	​	​
34​	Pregnant	PNEUMOCONJ13	​	​	​	​	​
35​	Pregnant	PNEUMOPOLY	​	​	​	​	​
36​	Resident	FLU	​	​	​	​	​
37​	Resident	FLUSIN	​	​	​	​	​
38​	Resident	PNEUMOCONJ13	​	​	​	​	​
39​	Resident	PNEUMOPOLY	​	​	​	​	​
40​	Stroke / TIA	FLU	​	​	​	​	​
41​	Stroke / TIA	FLUSIN	​	​	​	​	​
42​	Stroke / TIA	PNEUMOCONJ13	​	​	​	​	​
43​	Stroke / TIA	PNEUMOPOLY	​	​	​	​	​
44​	Immunosuppressed	FLU	​	​	​	​	​
45​	Immunosuppressed	FLUSIN	​	​	​	​	​
46​	Immunosuppressed	PNEUMOCONJ13	​	​	​	​	​
47​	Immunosuppressed	PNEUMOPOLY	​	​	​	​	​
48​	Chronic Liver Disease	FLU	​	​	​	​	​
49​	Chronic Liver Disease	FLUSIN	​	​	​	​	​
50​	Chronic Liver Disease	PNEUMOCONJ13	​	​	​	​	​
51​	Chronic Liver Disease	PNEUMOPOLY	​	​	​	​	​

 

[markmzz]

If I understand correctly what you want, this can helps:

Layout

ID

DOB

01/09/2013

Code

IMMSTYPE

STAGE

REASONTXT

Reason

Code

20/02/1944

69y 6m 12d

FLU1

FLU

Carer

ZFCARE

02/11/1910

102y 9m 30d

FLU2

FLU

Chronic Heart Disease

ZFCHD

21/09/1942

70y 11m 11d

FLU1

PNEUMOPOLY

Chronic Liver Disease

ZFCLD

12/11/1970

42y 9m 20d

ZFDM

FLU

Diabetes

Chronic Neurological Disease

ZFCNS

03/08/1963

50y 0m 29d

ZFCRES

FLU

Chronic Respiratory Disease

Chronic Renal Disease

ZFCRD

03/11/1962

50y 9m 29d

ZFSTIA

FLU

Stroke / Tia

Chronic Respiratory Disease

ZFCRES

17/03/1963

50y 5m 15d

ZFDM

FLU

Diabetes

Diabetes

ZFDM

15/01/1967

46y 7m 17d

ZFCRD

FLU

Chronic Renal Disease

Long-stay residential

ZFHOME

20/10/1969

43y 10m 12d

ZFCHD

FLU

Chronic Heart Disease

Immunosuppression

ZFIMM

05/10/1963

49y 10m 27d

ZFCNS

FLU

Chronic Neurological Disease

Pregnant

ZFPREG

13/04/1960

53y 4m 19d

ZFCRES

FLU

Chronic Respiratory Disease

Stroke / TIA

ZFSTIA

09/12/1969

43y 8m 23d

ZFSTIA

FLU

Stroke / Tia

Table -> N2:O12

28/10/1969

43y 10m 4d

Unidentified

FLU

01/09/2011

2y 0m 0d

ZFL2

FLUSIN

1

01/09/2010

3y 0m 0d

ZLF3

FLU

02/01/2005

8y 7m 30d

ZFCRES

FLUSIN

1

Chronic Respiratory Disease

08/01/2005

8y 7m 24d

ZFCRD

FLUSIN

1

Chronic Renal Disease

01/08/2011

2y 1m 0d

ZFL2

PNEUMOCONJ13

****

***********

************

************

**

**

**

**

**

***************

******

***************************

**

***************************

********

<colgroup><col width="27" style="width: 20pt; mso-width-source: userset; mso-width-alt: 987;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;" span="2"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;" span="5"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="147" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5376;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="147" style="width: 110pt; mso-width-source: userset; mso-width-alt: 5376;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <tbody>
</tbody>

Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula

D2-> =IF($B2 <= 1*"1/9/1938","FLU2",IF($B2 <= 1*"1/9/1948","FLU1",
IF(OR($L2=$N$2:$N$12),VLOOKUP($L2,$N$2:$O$12,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"))))))

Markmzz

 

[BradleyS]

Sorry for not being clearer. I can't change the table as when I have finished checking I have to submit it to another database and it has to be in this exact format.

The table rules work as follows:

If the Date of Birth (DOB) is over 65 it must be either FLU1 or FLU2 no matter what.
FLU1 is if they are 65 to 74 and FLU2 is older than 75 (so this just uses the DOB to work out their age)

The only reason I have added the date 01/09/2013 is because their age must be calculated after that date, i.e. they must be over 65 as at the 1/9/2013.

The next set of codes (ZFCARE, ZFCHD, ZFCLD, ZFCNS, ZFCRD, ZFCRES, ZFDM, ZFHOME, ZFIMM, ZFPREG
ZFSTIA) only apply to those under 65y and each MUST have a risk assigned to them, i.e CHD, Diabetes, etc... shown in column L (and the text is written exactly as shown in my formula, i.e. Carer, Chronic Heart Disease, Chronic Liver Disease, Chronic Neurological Disease, Chronic Renal Disease, Chronic Respiratory Disease, Diabetes, Long-stay residential, Immunosuppression, Pregnant, Stroke / TIA)

ZFL2 is assigned if there age is 2 with no risk code
ZFL3 is assigned if there age is 3 with no risk code
If there is a risk code for 2y and 3y it must show that risk code instead.

Lastly if the IMMTYPE in column J is PNEUMOPOLY (or not FLU) the code is PNEU
and if it shows PNEUMOCONJ13 the code is PNCHMG.

I added the UNIDENTIFIED message for all those that do not meet the exact rules I have explained above, mainly for the under 65ys with no risk code assigned.

I hope that helps. I will be extremly amazed if I can get this to work in one formula, so do let me know if it isn't possible.

 

[BradleyS]

I also noticed that if I paste the formula as text it looses virtually all the formula, which I think is what has happened in he last post. I think its the < and > causing the problem

 

[shg]

The table I suggested reduces your formula to a simple 2D lookup; it doesn't change anything else. At a glance, Mark's retains the flavor of your formula, and likewise doesn't change anything else.

The reason I suggested a table is that it encapsulates the logic in data rather than a formula.

EDIT:

I think its the < and > causing the problem

< followed by a letter is interpreted as an opening HTML tag. Add a space after it.

 

[BradleyS]

I agree with your table and it would make it easier, but I can't change the format of the table as it is required for further submission.
I'm given the data and then just have to manually change each code accordingly. I just thought I might be able to use a formula. I got so far with the IF's but as mentioned in xl2003 I can only use up to 7.

Hopefully Mark will repost his formula using a space as you mentioned so that I may see the whole formula, because he has kept the table format.

Thank you for your suggestion though.

 

[markmzz]

Hopefully Mark will repost his formula using a space as you mentioned so that I may see the whole formula, because he has kept the table format.

Thank you for your suggestion though.

Here you have a big array formula (without the table):

Use Ctrl+Shift+Enter and not only Enter to enter the formula

=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(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"))))))

PS: I'll try to create another formula for you.

Markmzz