| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If statement help needed
Hi;
I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
Hi;
Is there a limit of the if else criteria that I can put in for excel? I found that it doesn't allow me to put in more than 7 criterias. "ah" wrote: Hi; I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
I hope someone comes up with something better because this is messy but it
does solve the problem:- In A1=IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports",E12)),"F",""))) ))) In A2=IF(ISNUMBER(SEARCH("Sal",E12)),"G","") In A3=IF(AND(A1="",A2=""),"Not OK",CONCATENATE(A1,A2)) Mike "ah" wrote: Hi; I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
There is a limit of 7, although to can have work around.
Look at VLOOKUP as an alternative solution: create table in column A & B starting row 2 A B Cost Center A OM B etc =VLOOKUP(E12,$A$2:$A$10,2,0) For error condition: =IF(ISNA(VLOOKUP(E12,$A$2:$A$10,2,0)),"Not OK",VLOOKUP(E12,$A$2:$A$10,2,0)) HTH "ah" wrote: Hi; Is there a limit of the if else criteria that I can put in for excel? I found that it doesn't allow me to put in more than 7 criterias. "ah" wrote: Hi; I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
I get an error when I use the following statement:
=IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error I think you are missing a closing parenthesis on the end of your formula. Rick |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
Hi
Unless you are using XL2007, there is a limit of 7 for nested functions. If you create a list of your 7 items you are looking for in say cells A1:A7, then you could use =IF(SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,D1)))),"G","Not OK") Note I have used Find as this is case sensitive as opposed to Search. Clearly your list can be more than 7 items, just extend the range A1:A7 to cover the number of items concerned. -- Regards Roger Govier "ah" wrote in message ... Hi; I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
Ignore my reply .... as you are looking a sub-string: misread your question!
"Toppers" wrote: There is a limit of 7, although to can have work around. Look at VLOOKUP as an alternative solution: create table in column A & B starting row 2 A B Cost Center A OM B etc =VLOOKUP(E12,$A$2:$A$10,2,0) For error condition: =IF(ISNA(VLOOKUP(E12,$A$2:$A$10,2,0)),"Not OK",VLOOKUP(E12,$A$2:$A$10,2,0)) HTH "ah" wrote: Hi; Is there a limit of the if else criteria that I can put in for excel? I found that it doesn't allow me to put in more than 7 criterias. "ah" wrote: Hi; I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
Roger,
There is a list of corresponding values (A,B,C etc) with the search arguments so building on your solution with table in columns A & B: =IF(SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,$E$12)))*ROW($A$1:$A$7)), INDEX($B$1:$B$7,SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,$E$12)))*ROW($A$1:$A$7)), 0),"Not OK") I am sure you can improve on this! "Roger Govier" wrote: Hi Unless you are using XL2007, there is a limit of 7 for nested functions. If you create a list of your 7 items you are looking for in say cells A1:A7, then you could use =IF(SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,D1)))),"G","Not OK") Note I have used Find as this is case sensitive as opposed to Search. Clearly your list can be more than 7 items, just extend the range A1:A7 to cover the number of items concerned. -- Regards Roger Govier "ah" wrote in message ... Hi; I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
Hi;
Thanks for your help. But, I'm confused now. Let me tell you what I want so that you can assist me: I want the system to assign a value based on the following pre defined table: If Cell A1 contains the word called "Cost Center", then assign the value 0.5 to me If Cell A1 contains the word called "Department", then assign the value 1.0 to me If Cell A1 contains the word called "Bank", then assign the value 1.2 to me and etc FYI, I have about 30 criterias to fulfill. What are the best approach that I can use? Please advice. Thanks in advance "Toppers" wrote: There is a limit of 7, although to can have work around. Look at VLOOKUP as an alternative solution: create table in column A & B starting row 2 A B Cost Center A OM B etc =VLOOKUP(E12,$A$2:$A$10,2,0) For error condition: =IF(ISNA(VLOOKUP(E12,$A$2:$A$10,2,0)),"Not OK",VLOOKUP(E12,$A$2:$A$10,2,0)) HTH "ah" wrote: Hi; Is there a limit of the if else criteria that I can put in for excel? I found that it doesn't allow me to put in more than 7 criterias. "ah" wrote: Hi; I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
Does A1 only contain the text you want to test or is a sub-set e.g it could
contain "XYZ Cost Center"? "ah" wrote: Hi; Thanks for your help. But, I'm confused now. Let me tell you what I want so that you can assist me: I want the system to assign a value based on the following pre defined table: If Cell A1 contains the word called "Cost Center", then assign the value 0.5 to me If Cell A1 contains the word called "Department", then assign the value 1.0 to me If Cell A1 contains the word called "Bank", then assign the value 1.2 to me and etc FYI, I have about 30 criterias to fulfill. What are the best approach that I can use? Please advice. Thanks in advance "Toppers" wrote: There is a limit of 7, although to can have work around. Look at VLOOKUP as an alternative solution: create table in column A & B starting row 2 A B Cost Center A OM B etc =VLOOKUP(E12,$A$2:$A$10,2,0) For error condition: =IF(ISNA(VLOOKUP(E12,$A$2:$A$10,2,0)),"Not OK",VLOOKUP(E12,$A$2:$A$10,2,0)) HTH "ah" wrote: Hi; Is there a limit of the if else criteria that I can put in for excel? I found that it doesn't allow me to put in more than 7 criterias. "ah" wrote: Hi; I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
Hi John
Is it not just a case of =SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,D1)))*$B$1:$B$7) or am I missing something. -- Regards Roger Govier "Toppers" wrote in message ... Roger, There is a list of corresponding values (A,B,C etc) with the search arguments so building on your solution with table in columns A & B: =IF(SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,$E$12)))*ROW($A$1:$A$7)), INDEX($B$1:$B$7,SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,$E$12)))*ROW($A$1:$A$7)), 0),"Not OK") I am sure you can improve on this! "Roger Govier" wrote: Hi Unless you are using XL2007, there is a limit of 7 for nested functions. If you create a list of your 7 items you are looking for in say cells A1:A7, then you could use =IF(SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,D1)))),"G","Not OK") Note I have used Find as this is case sensitive as opposed to Search. Clearly your list can be more than 7 items, just extend the range A1:A7 to cover the number of items concerned. -- Regards Roger Govier "ah" wrote in message ... Hi; I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
Sorry the reference cell you are using is E12 so that should be
=SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$4,$E$12)))*$B$1:$B$4) -- Regards Roger Govier "Toppers" wrote in message ... Roger, There is a list of corresponding values (A,B,C etc) with the search arguments so building on your solution with table in columns A & B: =IF(SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,$E$12)))*ROW($A$1:$A$7)), INDEX($B$1:$B$7,SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,$E$12)))*ROW($A$1:$A$7)), 0),"Not OK") I am sure you can improve on this! "Roger Govier" wrote: Hi Unless you are using XL2007, there is a limit of 7 for nested functions. If you create a list of your 7 items you are looking for in say cells A1:A7, then you could use =IF(SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,D1)))),"G","Not OK") Note I have used Find as this is case sensitive as opposed to Search. Clearly your list can be more than 7 items, just extend the range A1:A7 to cover the number of items concerned. -- Regards Roger Govier "ah" wrote in message ... Hi; I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
See Roger's last reply.
"ah" wrote: Hi; Thanks for your help. But, I'm confused now. Let me tell you what I want so that you can assist me: I want the system to assign a value based on the following pre defined table: If Cell A1 contains the word called "Cost Center", then assign the value 0.5 to me If Cell A1 contains the word called "Department", then assign the value 1.0 to me If Cell A1 contains the word called "Bank", then assign the value 1.2 to me and etc FYI, I have about 30 criterias to fulfill. What are the best approach that I can use? Please advice. Thanks in advance "Toppers" wrote: There is a limit of 7, although to can have work around. Look at VLOOKUP as an alternative solution: create table in column A & B starting row 2 A B Cost Center A OM B etc =VLOOKUP(E12,$A$2:$A$10,2,0) For error condition: =IF(ISNA(VLOOKUP(E12,$A$2:$A$10,2,0)),"Not OK",VLOOKUP(E12,$A$2:$A$10,2,0)) HTH "ah" wrote: Hi; Is there a limit of the if else criteria that I can put in for excel? I found that it doesn't allow me to put in more than 7 criterias. "ah" wrote: Hi; I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
It is now that the second values are numeric (according to OP's last posting)!
"Roger Govier" wrote: Sorry the reference cell you are using is E12 so that should be =SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$4,$E$12)))*$B$1:$B$4) -- Regards Roger Govier "Toppers" wrote in message ... Roger, There is a list of corresponding values (A,B,C etc) with the search arguments so building on your solution with table in columns A & B: =IF(SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,$E$12)))*ROW($A$1:$A$7)), INDEX($B$1:$B$7,SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,$E$12)))*ROW($A$1:$A$7)), 0),"Not OK") I am sure you can improve on this! "Roger Govier" wrote: Hi Unless you are using XL2007, there is a limit of 7 for nested functions. If you create a list of your 7 items you are looking for in say cells A1:A7, then you could use =IF(SUMPRODUCT(--(ISNUMBER(FIND($A$1:$A$7,D1)))),"G","Not OK") Note I have used Find as this is case sensitive as opposed to Search. Clearly your list can be more than 7 items, just extend the range A1:A7 to cover the number of items concerned. -- Regards Roger Govier "ah" wrote in message ... Hi; I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
Hi;
Thanks for your reply. Yup, "cost center" is just part of the word. For example: US cost center - international, UK cost center - local and etc "Toppers" wrote: Does A1 only contain the text you want to test or is a sub-set e.g it could contain "XYZ Cost Center"? "ah" wrote: Hi; Thanks for your help. But, I'm confused now. Let me tell you what I want so that you can assist me: I want the system to assign a value based on the following pre defined table: If Cell A1 contains the word called "Cost Center", then assign the value 0.5 to me If Cell A1 contains the word called "Department", then assign the value 1.0 to me If Cell A1 contains the word called "Bank", then assign the value 1.2 to me and etc FYI, I have about 30 criterias to fulfill. What are the best approach that I can use? Please advice. Thanks in advance "Toppers" wrote: There is a limit of 7, although to can have work around. Look at VLOOKUP as an alternative solution: create table in column A & B starting row 2 A B Cost Center A OM B etc =VLOOKUP(E12,$A$2:$A$10,2,0) For error condition: =IF(ISNA(VLOOKUP(E12,$A$2:$A$10,2,0)),"Not OK",VLOOKUP(E12,$A$2:$A$10,2,0)) HTH "ah" wrote: Hi; Is there a limit of the if else criteria that I can put in for excel? I found that it doesn't allow me to put in more than 7 criterias. "ah" wrote: Hi; I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
Sorry, I'm totally lost now.
Can anyone guide me on how to go about this? I want the system to assign a value based on the following pre defined table: If Cell A1 contains the word called "Cost Center", then assign the value 0.5 to me If Cell A1 contains the word called "Department", then assign the value 1.0 to me If Cell A1 contains the word called "Bank", then assign the value 1.2 to me and etc FYI, I have about 30 criterias to fulfill. What are the best approach that I can use? thanks in advance Hi; Thanks for your reply. Yup, "cost center" is just part of the word. For example: US cost center - international, UK cost center - local and etc "Toppers" wrote: Does A1 only contain the text you want to test or is a sub-set e.g it could contain "XYZ Cost Center"? "ah" wrote: Hi; Thanks for your help. But, I'm confused now. Let me tell you what I want so that you can assist me: I want the system to assign a value based on the following pre defined table: If Cell A1 contains the word called "Cost Center", then assign the value 0.5 to me If Cell A1 contains the word called "Department", then assign the value 1.0 to me If Cell A1 contains the word called "Bank", then assign the value 1.2 to me and etc FYI, I have about 30 criterias to fulfill. What are the best approach that I can use? Please advice. Thanks in advance "Toppers" wrote: There is a limit of 7, although to can have work around. Look at VLOOKUP as an alternative solution: create table in column A & B starting row 2 A B Cost Center A OM B etc =VLOOKUP(E12,$A$2:$A$10,2,0) For error condition: =IF(ISNA(VLOOKUP(E12,$A$2:$A$10,2,0)),"Not OK",VLOOKUP(E12,$A$2:$A$10,2,0)) HTH "ah" wrote: Hi; Is there a limit of the if else criteria that I can put in for excel? I found that it doesn't allow me to put in more than 7 criterias. "ah" wrote: Hi; I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
If statement help needed
Hi
If that's the case, then maybe you would be better off with a Vlookup. Create a table on another Sheet (Sheet2) with your 30 or so items in A1:A30. Alongside each set the values in B1:B30 that you want assigned to each Mark the block of A1:B30 and in the small white pane above row number and left of column A (the name box) type myData and press Enter. On your Sheet1, in cell B1 =IF(A1="","",IF(ISERROR(VLOOKUP(A1,myTable,2,0))," ",VLOOKUP(A1,myTable,2,0))) Copy down column B as far as required. Now anything typed in A1, will return the value associated with that item. -- Regards Roger Govier "ah" wrote in message ... Sorry, I'm totally lost now. Can anyone guide me on how to go about this? I want the system to assign a value based on the following pre defined table: If Cell A1 contains the word called "Cost Center", then assign the value 0.5 to me If Cell A1 contains the word called "Department", then assign the value 1.0 to me If Cell A1 contains the word called "Bank", then assign the value 1.2 to me and etc FYI, I have about 30 criterias to fulfill. What are the best approach that I can use? thanks in advance Hi; Thanks for your reply. Yup, "cost center" is just part of the word. For example: US cost center - international, UK cost center - local and etc "Toppers" wrote: Does A1 only contain the text you want to test or is a sub-set e.g it could contain "XYZ Cost Center"? "ah" wrote: Hi; Thanks for your help. But, I'm confused now. Let me tell you what I want so that you can assist me: I want the system to assign a value based on the following pre defined table: If Cell A1 contains the word called "Cost Center", then assign the value 0.5 to me If Cell A1 contains the word called "Department", then assign the value 1.0 to me If Cell A1 contains the word called "Bank", then assign the value 1.2 to me and etc FYI, I have about 30 criterias to fulfill. What are the best approach that I can use? Please advice. Thanks in advance "Toppers" wrote: There is a limit of 7, although to can have work around. Look at VLOOKUP as an alternative solution: create table in column A & B starting row 2 A B Cost Center A OM B etc =VLOOKUP(E12,$A$2:$A$10,2,0) For error condition: =IF(ISNA(VLOOKUP(E12,$A$2:$A$10,2,0)),"Not OK",VLOOKUP(E12,$A$2:$A$10,2,0)) HTH "ah" wrote: Hi; Is there a limit of the if else criteria that I can put in for excel? I found that it doesn't allow me to put in more than 7 criterias. "ah" wrote: Hi; I get an error when I use the following statement: =IF(ISNUMBER(SEARCH("Cost Center",E12)),"A",IF(ISNUMBER(SEARCH("OM",E12)),"B ",IF(ISNUMBER(SEARCH("Bank Information",E12)),"C",IF(ISNUMBER(SEARCH("Departm ent",E12)),"D",IF(ISNUMBER(SEARCH("Promotion",E12) ),"E",IF(ISNUMBER(SEARCH("Reports To",E12)),"F",IF(ISNUMBER(SEARCH("SAL",E12)),"G"," Not OK"))))))) The error message is as follows: The formula you type contains an error Can anyone help me with this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help needed would this be an IF statement | Excel Worksheet Functions | |||
| Another IF statement needed | Excel Worksheet Functions | |||
| If Statement - Help Needed | Excel Discussion (Misc queries) | |||
| much needed If/or statement help | Excel Discussion (Misc queries) | |||
| If statement needed | Excel Worksheet Functions | |||
Linear Mode
