| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple IF Statements with Three Variables
Hi All,
After a thorough search I couldnt see anything related or similar, so unfortunately I have to ask the question. I have three variables I need to calculate: Software Build Hardware Type Project Phase The spreadsheet is a list of faults reported on the software. The goal is to list in the table which phase each fault is for, so we can do a total. I have done the preliminary list which is similar to this (for each hardware, software and phase): IF I1 = Hardware-EMS AND J1 = V07.01.00S00 OR J1 = V07.01.00S101 THEN print Phase 1 thats the basics of what I require, but I require it for 14 different combinations. I really do hope someone can help, as this would make such a difference to our reporting. Thank you in advance for any help at all. x |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF Statements with Three Variables
Perhaps you can post your preliminary list of the 14 conditions in
full ? It strikes me that you can build up a table of these combinations and then use a lookup formula to derive the phase from it, rather than multiple IFs, but will need to see what other combinations you might have. Pete On Jun 18, 11:19*am, Gemsera wrote: Hi All, After a thorough search I couldnt see anything related or similar, so unfortunately I have to ask the question. I have three variables I need to calculate: Software Build Hardware Type Project Phase The spreadsheet is a list of faults reported on the software. The goal is to list in the table which phase each fault is for, so we can do a total. I have done the preliminary list which is similar to this (for each hardware, software and phase): IF I1 = Hardware-EMS AND J1 = V07.01.00S00 * *OR J1 = V07.01.00S101 THEN print Phase 1 thats the basics of what I require, but I require it for 14 different combinations. I really do hope someone can help, as this would make such a difference to our reporting. Thank you in advance for any help at all. x |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF Statements with Three Variables
Thanks for such a quick response!
Here is the preliminary list: IF I1 = Hardware1 AND J1 = V07.01.00S00 OR J1 = V07.01.00S101 THEN print Phase 1 IF I1 = Hardware1 AND J1 = V07.01.00R000P3A004 THEN print Phase 2 IF I1 = Hardware2 AND J1 = V07.01.00S106 OR J1 = V07.01.00S107 THEN print Phase 1 IF I1 = Hardware2 AND J1 = V07.01.00S201 OR J1 = V07.01.00S202 OR J1 = V07.01.00A208 THEN print Phase 2 IF I1 = Hardware3 AND J1 = V07.01.00S106 OR J1 = V07.01.00S107 THEN print Phase 1 IF I1 = Hardware3 AND J1 = V07.01.00S202 OR J1 = V07.01.00A208 THEN print Phase 2 IF I1 = Hardware4 AND J1 = V07.01.00R001 THEN print Phase 1 IF I1 = Hardware4 AND J1 = V07.01.00A004 OR J1 = V07.01.00A007 THEN print Phase 2 IF I1 = Hardware5 AND J1 = 4.1 THEN print Phase 1 IF I1 = Hardware5 AND J1 = 4.2 THEN print Phase 2 IF I1 = Hardware6 AND J1 = V07.01.00R00 THEN print General Phase IF I1 = Hardware7 AND J1 = V07.01.00R01 THEN print Phase 1 IF I1 = Hardware7 AND J1 = V07.01.01A015 OR J1 = V07.01.01A020 THEN print Phase 2 IF I1 = Hardware8 AND J1 = V07.01.00S101 THEN print Phase 1 I have never used vlookup, so would appreciate advice :) "Pete_UK" wrote: Perhaps you can post your preliminary list of the 14 conditions in full ? It strikes me that you can build up a table of these combinations and then use a lookup formula to derive the phase from it, rather than multiple IFs, but will need to see what other combinations you might have. Pete On Jun 18, 11:19 am, Gemsera wrote: Hi All, After a thorough search I couldnt see anything related or similar, so unfortunately I have to ask the question. I have three variables I need to calculate: Software Build Hardware Type Project Phase The spreadsheet is a list of faults reported on the software. The goal is to list in the table which phase each fault is for, so we can do a total. I have done the preliminary list which is similar to this (for each hardware, software and phase): IF I1 = Hardware-EMS AND J1 = V07.01.00S00 OR J1 = V07.01.00S101 THEN print Phase 1 thats the basics of what I require, but I require it for 14 different combinations. I really do hope someone can help, as this would make such a difference to our reporting. Thank you in advance for any help at all. x |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF Statements with Three Variables
On Jun 18, 5:48 am, Gemsera wrote:
Thanks for such a quick response! Here is the preliminary list: IF I1 = Hardware1 AND J1 = V07.01.00S00 OR J1 = V07.01.00S101 THEN print Phase 1 IF I1 = Hardware1 AND J1 = V07.01.00R000P3A004 THEN print Phase 2 IF I1 = Hardware2 AND J1 = V07.01.00S106 OR J1 = V07.01.00S107 THEN print Phase 1 IF I1 = Hardware2 AND J1 = V07.01.00S201 OR J1 = V07.01.00S202 OR J1 = V07.01.00A208 THEN print Phase 2 IF I1 = Hardware3 AND J1 = V07.01.00S106 OR J1 = V07.01.00S107 THEN print Phase 1 IF I1 = Hardware3 AND J1 = V07.01.00S202 OR J1 = V07.01.00A208 THEN print Phase 2 IF I1 = Hardware4 AND J1 = V07.01.00R001 THEN print Phase 1 IF I1 = Hardware4 AND J1 = V07.01.00A004 OR J1 = V07.01.00A007 THEN print Phase 2 IF I1 = Hardware5 AND J1 = 4.1 THEN print Phase 1 IF I1 = Hardware5 AND J1 = 4.2 THEN print Phase 2 IF I1 = Hardware6 AND J1 = V07.01.00R00 THEN print General Phase IF I1 = Hardware7 AND J1 = V07.01.00R01 THEN print Phase 1 IF I1 = Hardware7 AND J1 = V07.01.01A015 OR J1 = V07.01.01A020 THEN print Phase 2 IF I1 = Hardware8 AND J1 = V07.01.00S101 THEN print Phase 1 I have never used vlookup, so would appreciate advice :) "Pete_UK" wrote: Perhaps you can post your preliminary list of the 14 conditions in full ? It strikes me that you can build up a table of these combinations and then use a lookup formula to derive the phase from it, rather than multiple IFs, but will need to see what other combinations you might have. Pete On Jun 18, 11:19 am, Gemsera wrote: Hi All, After a thorough search I couldnt see anything related or similar, so unfortunately I have to ask the question. I have three variables I need to calculate: Software Build Hardware Type Project Phase The spreadsheet is a list of faults reported on the software. The goal is to list in the table which phase each fault is for, so we can do a total. I have done the preliminary list which is similar to this (for each hardware, software and phase): IF I1 = Hardware-EMS AND J1 = V07.01.00S00 OR J1 = V07.01.00S101 THEN print Phase 1 thats the basics of what I require, but I require it for 14 different combinations. I really do hope someone can help, as this would make such a difference to our reporting. Thank you in advance for any help at all. x It looks to me like you actually have 22 hardware/software combinations listed there. I would make a simple database with 2 columns in a separate sheet. Assume in A1:B22 on Sheet2. Put every combination in the first column like this, just run together with no space between: Hardware1V07.01.00S00 Hardware1V07.01.00S101 Hardware2V07.01.00S106 Etc. Then the 2nd column is the appropriate Phase for each. Then your VLOOKUP is this: =VLOOKUP(I1&J1,Sheet2!$A$1:$B$22,2,FALSE) Uh, don't make any typos. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple IF Statements with Three Variables
It is a little more complicated by the fact that the XML isnt formatted in
that nature when the reports are done, but I can do that manually. Thank you for the enlightenment I desperately needed! "Spiky" wrote: On Jun 18, 5:48 am, Gemsera wrote: Thanks for such a quick response! Here is the preliminary list: IF I1 = Hardware1 AND J1 = V07.01.00S00 OR J1 = V07.01.00S101 THEN print Phase 1 IF I1 = Hardware1 AND J1 = V07.01.00R000P3A004 THEN print Phase 2 IF I1 = Hardware2 AND J1 = V07.01.00S106 OR J1 = V07.01.00S107 THEN print Phase 1 IF I1 = Hardware2 AND J1 = V07.01.00S201 OR J1 = V07.01.00S202 OR J1 = V07.01.00A208 THEN print Phase 2 IF I1 = Hardware3 AND J1 = V07.01.00S106 OR J1 = V07.01.00S107 THEN print Phase 1 IF I1 = Hardware3 AND J1 = V07.01.00S202 OR J1 = V07.01.00A208 THEN print Phase 2 IF I1 = Hardware4 AND J1 = V07.01.00R001 THEN print Phase 1 IF I1 = Hardware4 AND J1 = V07.01.00A004 OR J1 = V07.01.00A007 THEN print Phase 2 IF I1 = Hardware5 AND J1 = 4.1 THEN print Phase 1 IF I1 = Hardware5 AND J1 = 4.2 THEN print Phase 2 IF I1 = Hardware6 AND J1 = V07.01.00R00 THEN print General Phase IF I1 = Hardware7 AND J1 = V07.01.00R01 THEN print Phase 1 IF I1 = Hardware7 AND J1 = V07.01.01A015 OR J1 = V07.01.01A020 THEN print Phase 2 IF I1 = Hardware8 AND J1 = V07.01.00S101 THEN print Phase 1 I have never used vlookup, so would appreciate advice :) "Pete_UK" wrote: Perhaps you can post your preliminary list of the 14 conditions in full ? It strikes me that you can build up a table of these combinations and then use a lookup formula to derive the phase from it, rather than multiple IFs, but will need to see what other combinations you might have. Pete On Jun 18, 11:19 am, Gemsera wrote: Hi All, After a thorough search I couldnt see anything related or similar, so unfortunately I have to ask the question. I have three variables I need to calculate: Software Build Hardware Type Project Phase The spreadsheet is a list of faults reported on the software. The goal is to list in the table which phase each fault is for, so we can do a total. I have done the preliminary list which is similar to this (for each hardware, software and phase): IF I1 = Hardware-EMS AND J1 = V07.01.00S00 OR J1 = V07.01.00S101 THEN print Phase 1 thats the basics of what I require, but I require it for 14 different combinations. I really do hope someone can help, as this would make such a difference to our reporting. Thank you in advance for any help at all. x It looks to me like you actually have 22 hardware/software combinations listed there. I would make a simple database with 2 columns in a separate sheet. Assume in A1:B22 on Sheet2. Put every combination in the first column like this, just run together with no space between: Hardware1V07.01.00S00 Hardware1V07.01.00S101 Hardware2V07.01.00S106 Etc. Then the 2nd column is the appropriate Phase for each. Then your VLOOKUP is this: =VLOOKUP(I1&J1,Sheet2!$A$1:$B$22,2,FALSE) Uh, don't make any typos. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If,Then for multiple variables. | Excel Discussion (Misc queries) | |||
| Multiple variables-SOS | Excel Worksheet Functions | |||
| look for a value with multiple variables | Excel Discussion (Misc queries) | |||
| Sumif with multiple variables | Excel Discussion (Misc queries) | |||
| IF statements with more than 7 variables | Excel Worksheet Functions | |||
Linear Mode
