Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how many if statements can i use in a single cell ?
i have the folling formula, but excel doesn't seem to accept it
=IF(Leave!E5="A","A",IF(Leave!E5="I","I",IF(Leave! E5="S","S",IF(Leave!E5="L","L",IF(Leave!E5="T","T" ,IF(Leave!E5="R","R",IF(Leave!E6="A","A",IF(Leave! E6="I","I",IF(Leave!E6="S","S",IF(Leave!E6="L","L" ,IF(Leave!E6="T","T",IF(Leave!E6="R","R",IF(Leave! E7="A","x"))))))))))))) i need to have all those letters for each of the 5 cells being referenced... currently using Excel 2000 9.0.6926 SP-3. if there is a limitation.. how do i get around it ? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how many if statements can i use in a single cell ?
Hi!
This is my best guess at what you want: If any cell in the range Leave!E5:E9 contains any one of the letter variables, return that letter, otherwise, return "X". Only one cell may contain a letter. List the letter variables in a range somewhere, say, A1:A6. =IF(SUMPRODUCT(COUNTIF(A1:A6,Leave!E5:E9)),INDEX(L eave!E5:E9,MATCH("*",Leave!E5:E9,0)),"X") There is a limit of 7 nested levels of functions. you had 12. Biff "Kypreo" wrote in message ... i have the folling formula, but excel doesn't seem to accept it =IF(Leave!E5="A","A",IF(Leave!E5="I","I",IF(Leave! E5="S","S",IF(Leave!E5="L","L",IF(Leave!E5="T","T" ,IF(Leave!E5="R","R",IF(Leave!E6="A","A",IF(Leave! E6="I","I",IF(Leave!E6="S","S",IF(Leave!E6="L","L" ,IF(Leave!E6="T","T",IF(Leave!E6="R","R",IF(Leave! E7="A","x"))))))))))))) i need to have all those letters for each of the 5 cells being referenced... currently using Excel 2000 9.0.6926 SP-3. if there is a limitation.. how do i get around it ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how many if statements can i use in a single cell ?
Hi
One way would be the array formula {=CHAR(MIN(IF(ISTEXT(Leave!E5:E9&"X"),CODE(Leave!E 5:E9&"X"))))} Commit with Ctrl+Shift+Enter not just Enter when entering or modifying the formula. Excel will include the curly braces { } for you, do not type them yourself. Regards Roger Govier Kypreo wrote: i have the folling formula, but excel doesn't seem to accept it =IF(Leave!E5="A","A",IF(Leave!E5="I","I",IF(Leave! E5="S","S",IF(Leave!E5="L","L",IF(Leave!E5="T","T" ,IF(Leave!E5="R","R",IF(Leave!E6="A","A",IF(Leave! E6="I","I",IF(Leave!E6="S","S",IF(Leave!E6="L","L" ,IF(Leave!E6="T","T",IF(Leave!E6="R","R",IF(Leave! E7="A","x"))))))))))))) i need to have all those letters for each of the 5 cells being referenced... currently using Excel 2000 9.0.6926 SP-3. if there is a limitation.. how do i get around it ? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how many if statements can i use in a single cell ?
One way would be the array formula
{=CHAR(MIN(IF(ISTEXT(Leave!E5:E9&"X"),CODE(Leave!E 5:E9&"X"))))} That's case sensitive and also allows for entries other than the 6 specific variables (including numbers). I guess data validation could take care of that. a returns X B returns B 3 returns 3 Biff "Roger Govier" wrote in message ... Hi One way would be the array formula {=CHAR(MIN(IF(ISTEXT(Leave!E5:E9&"X"),CODE(Leave!E 5:E9&"X"))))} Commit with Ctrl+Shift+Enter not just Enter when entering or modifying the formula. Excel will include the curly braces { } for you, do not type them yourself. Regards Roger Govier Kypreo wrote: i have the folling formula, but excel doesn't seem to accept it =IF(Leave!E5="A","A",IF(Leave!E5="I","I",IF(Leave! E5="S","S",IF(Leave!E5="L","L",IF(Leave!E5="T","T" ,IF(Leave!E5="R","R",IF(Leave!E6="A","A",IF(Leave! E6="I","I",IF(Leave!E6="S","S",IF(Leave!E6="L","L" ,IF(Leave!E6="T","T",IF(Leave!E6="R","R",IF(Leave! E7="A","x"))))))))))))) i need to have all those letters for each of the 5 cells being referenced... currently using Excel 2000 9.0.6926 SP-3. if there is a limitation.. how do i get around it ? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how many if statements can i use in a single cell ?
Hi Biff
If the data isn't already being validated then {=CHAR(MIN(IF(CODE(Leave!E5:E9&"X")64,CODE(UPPER( Leave!E5:E9&"X"))))))} should do the trick. Regards Roger Govier Biff wrote: One way would be the array formula {=CHAR(MIN(IF(ISTEXT(Leave!E5:E9&"X"),CODE(Leave !E5:E9&"X"))))} That's case sensitive and also allows for entries other than the 6 specific variables (including numbers). I guess data validation could take care of that. a returns X B returns B 3 returns 3 Biff "Roger Govier" wrote in message ... Hi One way would be the array formula {=CHAR(MIN(IF(ISTEXT(Leave!E5:E9&"X"),CODE(Leave !E5:E9&"X"))))} Commit with Ctrl+Shift+Enter not just Enter when entering or modifying the formula. Excel will include the curly braces { } for you, do not type them yourself. Regards Roger Govier Kypreo wrote: i have the folling formula, but excel doesn't seem to accept it =IF(Leave!E5="A","A",IF(Leave!E5="I","I",IF(Lea ve!E5="S","S",IF(Leave!E5="L","L",IF(Leave!E5="T", "T",IF(Leave!E5="R","R",IF(Leave!E6="A","A",IF(Lea ve!E6="I","I",IF(Leave!E6="S","S",IF(Leave!E6="L", "L",IF(Leave!E6="T","T",IF(Leave!E6="R","R",IF(Lea ve!E7="A","x"))))))))))))) i need to have all those letters for each of the 5 cells being referenced... currently using Excel 2000 9.0.6926 SP-3. if there is a limitation.. how do i get around it ? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how many if statements can i use in a single cell ?
Hi
Meant to say, will take out numerics and lower case, will still not trap for upper alpha's not in required range of values, but they could be accounted for by Data Validation. Regards Roger Govier Roger Govier wrote: Hi Biff If the data isn't already being validated then {=CHAR(MIN(IF(CODE(Leave!E5:E9&"X")64,CODE(UPPER( Leave!E5:E9&"X"))))))} should do the trick. Regards Roger Govier Biff wrote: One way would be the array formula {=CHAR(MIN(IF(ISTEXT(Leave!E5:E9&"X"),CODE(Leave!E 5:E9&"X"))))} That's case sensitive and also allows for entries other than the 6 specific variables (including numbers). I guess data validation could take care of that. a returns X B returns B 3 returns 3 Biff "Roger Govier" wrote in message ... Hi One way would be the array formula {=CHAR(MIN(IF(ISTEXT(Leave!E5:E9&"X"),CODE(Leave!E 5:E9&"X"))))} Commit with Ctrl+Shift+Enter not just Enter when entering or modifying the formula. Excel will include the curly braces { } for you, do not type them yourself. Regards Roger Govier Kypreo wrote: i have the folling formula, but excel doesn't seem to accept it =IF(Leave!E5="A","A",IF(Leave!E5="I","I",IF(Leave! E5="S","S",IF(Leave!E5="L","L",IF(Leave!E5="T","T" ,IF(Leave!E5="R","R",IF(Leave!E6="A","A",IF(Leave! E6="I","I",IF(Leave!E6="S","S",IF(Leave!E6="L","L" ,IF(Leave!E6="T","T",IF(Leave!E6="R","R",IF(Leave! E7="A","x"))))))))))))) i need to have all those letters for each of the 5 cells being referenced... currently using Excel 2000 9.0.6926 SP-3. if there is a limitation.. how do i get around it ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting Word table cell with paragraph markers into single Excel c | Excel Discussion (Misc queries) | |||
Splitting a single cell | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
Toggle multiple values in single cell | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |