BradleyS
Active Member
- Joined
- Oct 28, 2006
- Messages
- 333
- Office Version
- 2010
- Platform
- Windows
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:
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
Here are the rules that make up the code in column D
Any help in this would be most appreciated as I have hundreds to check through
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