|
IF Worksheet Function
I've created the following formula,
=IF(AND(D3="Back Up",E3="ABCD04C00",F3<=6),"10.10.1.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C01",F3<=6),"10.10.2.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C02",F3<=6),"10.10.3.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C03",F3<=6),"10.10.4.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C04",F3<=6),"10.10.5.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C05",F3<=6),"10.10.6.0 /22","0") The first expression from =sign to )parenthesis, works well, however, when I add the additional expressions, I get the following result #VALUE!. I need to know how to make this work to allow me to obtain a variety of TRUE results. I will need to add approximately 100 expressions. If these is a more efficient way to accomplish this, I'm all ears. All suggestions are welcome. |
Each IF() is a complete standalone & you've tried to put them together
separated by commas. So, the first two should be combined like so: =IF(AND(D3="Back Up",E3="ABCD04C00",F3<=6),"10.10.1.0 /22",IF(AND(D3="Back Up",E3="ABCD04C01",F3<=6),"10.10.2.0 /22", - next if() - and so on However, everything you've listed can (almost) be simplified to this: =IF(AND(D3="Back Up",left(E3,8)="ABCD04C0",F3<=6),"10.10."&right(E3 ,1)&".0 /22","0") The (almost) is there because the left 8 characters of E3 might fit the pattern but not meet one of your specified conditions. Another way to do this would be to list the possible E3 entries in one column and the desired result in the adjacent, right-hand column. Then your formula could be: =IF(AND(D3="Back Up",F3<=6),VLOOKUP(E3, two-column range, 2,0,"") "JB" wrote: I've created the following formula, =IF(AND(D3="Back Up",E3="ABCD04C00",F3<=6),"10.10.1.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C01",F3<=6),"10.10.2.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C02",F3<=6),"10.10.3.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C03",F3<=6),"10.10.4.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C04",F3<=6),"10.10.5.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C05",F3<=6),"10.10.6.0 /22","0") The first expression from =sign to )parenthesis, works well, however, when I add the additional expressions, I get the following result #VALUE!. I need to know how to make this work to allow me to obtain a variety of TRUE results. I will need to add approximately 100 expressions. If these is a more efficient way to accomplish this, I'm all ears. All suggestions are welcome. |
You can only have up to 7 nested if staements, but you can probably simplify
your equation a bunch. From this example it looks as though D3 must alsways = "Back Up" and F3 always must be <= 6 ir looks like E3 is "ABCD04C" plus a 2 digit number. and it looks like the output is always "10.10.X.0/22" where x is the two digit number from E3 +1 with no leading zeros. (This doesn't fit with you saying you need to add about hundred more conditions but) try =if(and(D3="Back Up",F3<=6,Left(E3,7)="ABCD04C"),"10.10."&value(rig ht(E3,2))&".0/22","0") "JB" wrote: I've created the following formula, =IF(AND(D3="Back Up",E3="ABCD04C00",F3<=6),"10.10.1.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C01",F3<=6),"10.10.2.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C02",F3<=6),"10.10.3.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C03",F3<=6),"10.10.4.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C04",F3<=6),"10.10.5.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C05",F3<=6),"10.10.6.0 /22","0") The first expression from =sign to )parenthesis, works well, however, when I add the additional expressions, I get the following result #VALUE!. I need to know how to make this work to allow me to obtain a variety of TRUE results. I will need to add approximately 100 expressions. If these is a more efficient way to accomplish this, I'm all ears. All suggestions are welcome. |
On Tue, 24 May 2005 10:10:02 -0700, "JB" wrote:
I've created the following formula, =IF(AND(D3="Back Up",E3="ABCD04C00",F3<=6),"10.10.1.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C01",F3<=6),"10.10.2.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C02",F3<=6),"10.10.3.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C03",F3<=6),"10.10.4.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C04",F3<=6),"10.10.5.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C05",F3<=6),"10.10.6.0 /22","0") The first expression from =sign to )parenthesis, works well, however, when I add the additional expressions, I get the following result #VALUE!. I need to know how to make this work to allow me to obtain a variety of TRUE results. I will need to add approximately 100 expressions. If these is a more efficient way to accomplish this, I'm all ears. All suggestions are welcome. So far as this formula is concerned, your syntax is messed up as others have pointed out. However, your approach seems inefficient. For example, given what you have posted, a more efficient approach would be to use a lookup function. For example: =IF(AND(D3="Back Up", F3<=6),VLOOKUP (your_string,lookup_table,2,FALSE),0) lookup_table would be a list of your strings followed by the corresponding code. ABCD04C00 10.10.1.0 /22 ABCD04C01 10.10.2.0 /22 ABCD04C02 10.10.3.0 /22 etc. --ron |
First of all, Thanks bj for your reply..
Actually; I regularly make a spreadsheet for each project I work on, which can vary from 3 to 200 rows. Each row describes a different network connection, and as you see this can be time consuming. Therefore, I'll be using the finished formula in each row. I hope this additional info will make things clearer. Please note: D3(Type of connection e.g. Back Up-1, RIB, DH-1) will have a total of about 10 different conditions, populated by drop down. E3(Name of switch e.g. ABCDxxxxxxx) will have approx 100 different conditions, where "ABCD" plus 5-7 digit number. F3(Card in switch e.g.xx/port) will have a total of 15 different 1-2 digit conditions. G3(Ports on card card/xx) will have a total of 48 different 1-2 digit conditions. H3(various IP segments e.g.xxx.xxx.xxx.xxx /xx) will have approx 100 different conditions. Various combinations of D3 - G3, needs to produce any one of 100 specific H3 outputs, so the same combinations will always produce the same output. Thanks for your help, again. "bj" wrote: You can only have up to 7 nested if staements, but you can probably simplify your equation a bunch. From this example it looks as though D3 must alsways = "Back Up" and F3 always must be <= 6 ir looks like E3 is "ABCD04C" plus a 2 digit number. and it looks like the output is always "10.10.X.0/22" where x is the two digit number from E3 +1 with no leading zeros. (This doesn't fit with you saying you need to add about hundred more conditions but) try =if(and(D3="Back Up",F3<=6,Left(E3,7)="ABCD04C"),"10.10."&value(rig ht(E3,2))&".0/22","0") "JB" wrote: I've created the following formula, =IF(AND(D3="Back Up",E3="ABCD04C00",F3<=6),"10.10.1.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C01",F3<=6),"10.10.2.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C02",F3<=6),"10.10.3.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C03",F3<=6),"10.10.4.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C04",F3<=6),"10.10.5.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C05",F3<=6),"10.10.6.0 /22","0") The first expression from =sign to )parenthesis, works well, however, when I add the additional expressions, I get the following result #VALUE!. I need to know how to make this work to allow me to obtain a variety of TRUE results. I will need to add approximately 100 expressions. If these is a more efficient way to accomplish this, I'm all ears. All suggestions are welcome. |
First of all, Thanks Duke for your reply..
Actually; I regularly make a spreadsheet for each project I work on, which can vary from 3 to 200 rows. Each row describes a different network connection. And as you see, this can be time consuming. Therefore, I'll be using the finished formula in each row. Here is additional info to make things clearer. Please note: D3(Type of connection e.g. Back Up-1, RIB, DH-1) will have a total of about 10 different conditions, populated by drop down. E3(Name of switch e.g. ABCDxxxxxxx) will have approx 100 different conditions, where "ABCD" plus 5-7 digit number. F3(Card in switch e.g.xx/port) will have a total of 15 different 1-2 digit conditions. G3(Ports on card card/xx) will have a total of 48 different 1-2 digit conditions. H3(various IP segments e.g.xxx.xxx.xxx.xxx /xx) will have approx 100 different conditions. Various combinations of D3 - G3, needs to produce any one of 100 specific H3 outputs, so the same combinations will always produce the same output. Thanks for your help, again. "Duke Carey" wrote: Each IF() is a complete standalone & you've tried to put them together separated by commas. So, the first two should be combined like so: =IF(AND(D3="Back Up",E3="ABCD04C00",F3<=6),"10.10.1.0 /22",IF(AND(D3="Back Up",E3="ABCD04C01",F3<=6),"10.10.2.0 /22", - next if() - and so on However, everything you've listed can (almost) be simplified to this: =IF(AND(D3="Back Up",left(E3,8)="ABCD04C0",F3<=6),"10.10."&right(E3 ,1)&".0 /22","0") The (almost) is there because the left 8 characters of E3 might fit the pattern but not meet one of your specified conditions. Another way to do this would be to list the possible E3 entries in one column and the desired result in the adjacent, right-hand column. Then your formula could be: =IF(AND(D3="Back Up",F3<=6),VLOOKUP(E3, two-column range, 2,0,"") "JB" wrote: I've created the following formula, =IF(AND(D3="Back Up",E3="ABCD04C00",F3<=6),"10.10.1.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C01",F3<=6),"10.10.2.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C02",F3<=6),"10.10.3.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C03",F3<=6),"10.10.4.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C04",F3<=6),"10.10.5.0 /22","0"), IF(AND(D3="Back Up",E3="ABCD04C05",F3<=6),"10.10.6.0 /22","0") The first expression from =sign to )parenthesis, works well, however, when I add the additional expressions, I get the following result #VALUE!. I need to know how to make this work to allow me to obtain a variety of TRUE results. I will need to add approximately 100 expressions. If these is a more efficient way to accomplish this, I'm all ears. All suggestions are welcome. |
| All times are GMT +1. The time now is 09:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com