Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > IF Worksheet Function
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
JB
 
Posts: n/a
Default 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.
Reply With QuoteReply With Quote
  #2   Report Post  
Duke Carey
 
Posts: n/a
Default
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.
Reply With QuoteReply With Quote
  #3   Report Post  
bj
 
Posts: n/a
Default
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.
Reply With QuoteReply With Quote
  #4   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default
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
Reply With QuoteReply With Quote
  #5   Report Post  
JB
 
Posts: n/a
Default
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.
Reply With QuoteReply With Quote
  #6   Report Post  
JB
 
Posts: n/a
Default
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.
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatically up date time in a cell Mark Excel Discussion (Misc queries) 5 May 12th 05 12:26 AM
SUMIF function referring to values on different Worksheet TeeBee0831 Excel Worksheet Functions 10 May 3rd 05 10:28 PM
Hyperlinks using R[1]C[1] and offset function in its cell referenc Elijah-Dadda Excel Worksheet Functions 0 March 5th 05 03:31 AM
The Help on the Excel Edate worksheet function contains an error:. Guido Excel Worksheet Functions 1 January 20th 05 01:42 PM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. Robert AS Excel Worksheet Functions 4 December 2nd 04 10:49 AM


All times are GMT +1. The time now is 08:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement