| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested If workaround - How does it work for other cells dynamicall
I used the following link and solved the problem of using more than 7 nested
if statements http://www.cpearson.com/excel/nested.htm. I now have 3 defined names (3 formulas). Master, onetoseven and eighttonine. Master is onetoseven and eighttonine combined. I am using Master in cell C1. Now i need to use Master in cell C2, C3, .....etc for hundreds of rows. My problem is 'onetoseven' has IF(AND(A1="H",B1="H"),"H", IF(AND(A1="H",B1="M"),"H", IF(AND(A1="H",B1="L"),"M", IF(AND(A1="M",B1="H"),"M", IF(AND(A1="M",B1="M"),"M", IF(AND(A1="M",B1="L"),"L", IF(AND(A1="L",B1="H"),"M", ))))))) and 'eighttonine' has IF(AND(A1="L",B1="M"),"L", IF(AND(A1="L",B1="L"),"L",)) Now in Cell C2 i need to get IF statements of A2, B2, In C3 i have to get for A3,B3... and so forth. dynamically.. Is there a solution to this. Regards Rajula |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If workaround - How does it work for other cells dynamicall
Since each one of these conditions is mutually exclusive, you could just
concatenate those strings: =IF(AND(A1="H",B1="H"),"H","") &IF(AND(A1="H",B1="M"),"H","") &IF(AND(A1="H",B1="L"),"M","") &IF(AND(A1="M",B1="H"),"M","") &IF(AND(A1="M",B1="M"),"M","") &IF(AND(A1="M",B1="L"),"L","") &IF(AND(A1="L",B1="H"),"M","") (and so forth) Rajula wrote: I used the following link and solved the problem of using more than 7 nested if statements http://www.cpearson.com/excel/nested.htm. I now have 3 defined names (3 formulas). Master, onetoseven and eighttonine. Master is onetoseven and eighttonine combined. I am using Master in cell C1. Now i need to use Master in cell C2, C3, ....etc for hundreds of rows. My problem is 'onetoseven' has IF(AND(A1="H",B1="H"),"H", IF(AND(A1="H",B1="M"),"H", IF(AND(A1="H",B1="L"),"M", IF(AND(A1="M",B1="H"),"M", IF(AND(A1="M",B1="M"),"M", IF(AND(A1="M",B1="L"),"L", IF(AND(A1="L",B1="H"),"M", ))))))) and 'eighttonine' has IF(AND(A1="L",B1="M"),"L", IF(AND(A1="L",B1="L"),"L",)) Now in Cell C2 i need to get IF statements of A2, B2, In C3 i have to get for A3,B3... and so forth. dynamically.. Is there a solution to this. Regards Rajula -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If workaround - How does it work for other cells dynami
Hi,
It giving all kinds of funny problems. Out of the 3 defined formula, Only onetoseven works. eighttonine formula doesnt work using master. Formula doesnt work for A1, B1 or A2,B2 etc.... Bcos in the defined names the formula is for A1, B1... Regards "Dave Peterson" wrote: Since each one of these conditions is mutually exclusive, you could just concatenate those strings: =IF(AND(A1="H",B1="H"),"H","") &IF(AND(A1="H",B1="M"),"H","") &IF(AND(A1="H",B1="L"),"M","") &IF(AND(A1="M",B1="H"),"M","") &IF(AND(A1="M",B1="M"),"M","") &IF(AND(A1="M",B1="L"),"L","") &IF(AND(A1="L",B1="H"),"M","") (and so forth) Rajula wrote: I used the following link and solved the problem of using more than 7 nested if statements http://www.cpearson.com/excel/nested.htm. I now have 3 defined names (3 formulas). Master, onetoseven and eighttonine. Master is onetoseven and eighttonine combined. I am using Master in cell C1. Now i need to use Master in cell C2, C3, ....etc for hundreds of rows. My problem is 'onetoseven' has IF(AND(A1="H",B1="H"),"H", IF(AND(A1="H",B1="M"),"H", IF(AND(A1="H",B1="L"),"M", IF(AND(A1="M",B1="H"),"M", IF(AND(A1="M",B1="M"),"M", IF(AND(A1="M",B1="L"),"L", IF(AND(A1="L",B1="H"),"M", ))))))) and 'eighttonine' has IF(AND(A1="L",B1="M"),"L", IF(AND(A1="L",B1="L"),"L",)) Now in Cell C2 i need to get IF statements of A2, B2, In C3 i have to get for A3,B3... and so forth. dynamically.. Is there a solution to this. Regards Rajula -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If workaround - How does it work for other cells dynamicall
Hi Rajula, If you are having problems with multiple If statements matching the various combinations in A1 and B1, have you considered the Index function as an alternative. As per the attached, you can set any values into A and B and select which corresponding values will be valid or will be ignored, and you can add new items to the table sufficient to cover your needs. The statement then becomes: =IF(ISERROR(OR(MATCH(A1,F$10:F$14,0),MATCH(B1,G$9: K$9,0))),"",INDEX(G$10:K$14,MATCH(A1,F$10:F$14,0), MATCH(B1,G$9:K$9,0))) Just another option which could suit your needs and remove the guesswork out of what result a combination pair (A+Bn) will produce. attachment: HTH Rajula Wrote: Hi, It giving all kinds of funny problems. Out of the 3 defined formula, Only onetoseven works. eighttonine formula doesnt work using master. Formula doesnt work for A1, B1 or A2,B2 etc.... Bcos in the defined names the formula is for A1, B1... Regards "Dave Peterson" wrote: Since each one of these conditions is mutually exclusive, you could just concatenate those strings: =IF(AND(A1="H",B1="H"),"H","") &IF(AND(A1="H",B1="M"),"H","") &IF(AND(A1="H",B1="L"),"M","") &IF(AND(A1="M",B1="H"),"M","") &IF(AND(A1="M",B1="M"),"M","") &IF(AND(A1="M",B1="L"),"L","") &IF(AND(A1="L",B1="H"),"M","") (and so forth) Rajula wrote: I used the following link and solved the problem of using more than 7 nested if statements http://www.cpearson.com/excel/nested.htm. I now have 3 defined names (3 formulas). Master, onetoseven and eighttonine. Master is onetoseven and eighttonine combined. I am using Master in cell C1. Now i need to use Master in cell C2, C3, ....etc for hundreds of rows. My problem is 'onetoseven' has IF(AND(A1="H",B1="H"),"H", IF(AND(A1="H",B1="M"),"H", IF(AND(A1="H",B1="L"),"M", IF(AND(A1="M",B1="H"),"M", IF(AND(A1="M",B1="M"),"M", IF(AND(A1="M",B1="L"),"L", IF(AND(A1="L",B1="H"),"M", ))))))) and 'eighttonine' has IF(AND(A1="L",B1="M"),"L", IF(AND(A1="L",B1="L"),"L",)) Now in Cell C2 i need to get IF statements of A2, B2, In C3 i have to get for A3,B3... and so forth. dynamically.. Is there a solution to this. Regards Rajula -- Dave Peterson -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534670 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If workaround - How does it work for other cells dynamicall
Hi Rajula, If you are still having problems, have you considered using the Index function to select matches of A1 & B1. As per the attached, you can allocate values to various combinations, shown as green for A1 and purple for B1 to produce the result you require shown as yellow. The statement is: =IF(ISERROR(OR(MATCH(A1,F$10:F$14,0),MATCH(B1,G$9: K$9,0))),"",INDEX(G$10:K$14,MATCH(A1,F$10:F$14,0), MATCH(B1,G$9:K$9,0))) and the table can be increased to match your current and future needs, as is shown in the coloured zone. Just another possibility. Attached: http://www.excelforum.com/attachment...3&d=1145621998 Hope this helps Rajula Wrote: Hi, It giving all kinds of funny problems. Out of the 3 defined formula, Only onetoseven works. eighttonine formula doesnt work using master. Formula doesnt work for A1, B1 or A2,B2 etc.... Bcos in the defined names the formula is for A1, B1... Regards "Dave Peterson" wrote: Since each one of these conditions is mutually exclusive, you could just concatenate those strings: =IF(AND(A1="H",B1="H"),"H","") &IF(AND(A1="H",B1="M"),"H","") &IF(AND(A1="H",B1="L"),"M","") &IF(AND(A1="M",B1="H"),"M","") &IF(AND(A1="M",B1="M"),"M","") &IF(AND(A1="M",B1="L"),"L","") &IF(AND(A1="L",B1="H"),"M","") (and so forth) Rajula wrote: I used the following link and solved the problem of using more than 7 nested if statements http://www.cpearson.com/excel/nested.htm. I now have 3 defined names (3 formulas). Master, onetoseven and eighttonine. Master is onetoseven and eighttonine combined. I am using Master in cell C1. Now i need to use Master in cell C2, C3, ....etc for hundreds of rows. My problem is 'onetoseven' has IF(AND(A1="H",B1="H"),"H", IF(AND(A1="H",B1="M"),"H", IF(AND(A1="H",B1="L"),"M", IF(AND(A1="M",B1="H"),"M", IF(AND(A1="M",B1="M"),"M", IF(AND(A1="M",B1="L"),"L", IF(AND(A1="L",B1="H"),"M", ))))))) and 'eighttonine' has IF(AND(A1="L",B1="M"),"L", IF(AND(A1="L",B1="L"),"L",)) Now in Cell C2 i need to get IF statements of A2, B2, In C3 i have to get for A3,B3... and so forth. dynamically.. Is there a solution to this. Regards Rajula -- Dave Peterson +-------------------------------------------------------------------+ |Filename: Mif.zip | |Download: http://www.excelforum.com/attachment.php?postid=4673 | +-------------------------------------------------------------------+ -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534670 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If workaround - How does it work for other cells dynami
Hi Bryan,
Let me explain the problem. There are 3 columns A, B, C A can be High, Med, Low. B Can be High, Med, Low. If A is High & B is High, then C is High. If A is High & B is Med, then C is High. If A is High and B is Low, then C is medium, If A is Med & B is High, then C is High, If A is Med & B is Med, then C is Med..,.... so on an so forth.. Columns A & B i am giving a dropdown to select from High, Med, Low. And based on the values selected in A & B, C should get Values High, Med, Low. This is the actual problem. Regards "Bryan Hessey" wrote: Hi Rajula, If you are still having problems, have you considered using the Index function to select matches of A1 & B1. As per the attached, you can allocate values to various combinations, shown as green for A1 and purple for B1 to produce the result you require shown as yellow. The statement is: =IF(ISERROR(OR(MATCH(A1,F$10:F$14,0),MATCH(B1,G$9: K$9,0))),"",INDEX(G$10:K$14,MATCH(A1,F$10:F$14,0), MATCH(B1,G$9:K$9,0))) and the table can be increased to match your current and future needs, as is shown in the coloured zone. Just another possibility. Attached: http://www.excelforum.com/attachment...3&d=1145621998 Hope this helps Rajula Wrote: Hi, It giving all kinds of funny problems. Out of the 3 defined formula, Only onetoseven works. eighttonine formula doesnt work using master. Formula doesnt work for A1, B1 or A2,B2 etc.... Bcos in the defined names the formula is for A1, B1... Regards "Dave Peterson" wrote: Since each one of these conditions is mutually exclusive, you could just concatenate those strings: =IF(AND(A1="H",B1="H"),"H","") &IF(AND(A1="H",B1="M"),"H","") &IF(AND(A1="H",B1="L"),"M","") &IF(AND(A1="M",B1="H"),"M","") &IF(AND(A1="M",B1="M"),"M","") &IF(AND(A1="M",B1="L"),"L","") &IF(AND(A1="L",B1="H"),"M","") (and so forth) Rajula wrote: I used the following link and solved the problem of using more than 7 nested if statements http://www.cpearson.com/excel/nested.htm. I now have 3 defined names (3 formulas). Master, onetoseven and eighttonine. Master is onetoseven and eighttonine combined. I am using Master in cell C1. Now i need to use Master in cell C2, C3, ....etc for hundreds of rows. My problem is 'onetoseven' has IF(AND(A1="H",B1="H"),"H", IF(AND(A1="H",B1="M"),"H", IF(AND(A1="H",B1="L"),"M", IF(AND(A1="M",B1="H"),"M", IF(AND(A1="M",B1="M"),"M", IF(AND(A1="M",B1="L"),"L", IF(AND(A1="L",B1="H"),"M", ))))))) and 'eighttonine' has IF(AND(A1="L",B1="M"),"L", IF(AND(A1="L",B1="L"),"L",)) Now in Cell C2 i need to get IF statements of A2, B2, In C3 i have to get for A3,B3... and so forth. dynamically.. Is there a solution to this. Regards Rajula -- Dave Peterson +-------------------------------------------------------------------+ |Filename: Mif.zip | |Download: http://www.excelforum.com/attachment.php?postid=4673 | +-------------------------------------------------------------------+ -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534670 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If workaround - How does it work for other cells dynamicall
Rajula, Irrespective of how you get the contents into cells A or B, the Index/Match will set column C accordingly as per the Mif.wks. You can use a dropdown that's fine. The table reflects the values for where A value crosses with B value, and this will be selected when A1 and B1 are set. -- Rajula Wrote: Hi Bryan, Let me explain the problem. There are 3 columns A, B, C A can be High, Med, Low. B Can be High, Med, Low. If A is High & B is High, then C is High. If A is High & B is Med, then C is High. If A is High and B is Low, then C is medium, If A is Med & B is High, then C is High, If A is Med & B is Med, then C is Med..,.... so on an so forth.. Columns A & B i am giving a dropdown to select from High, Med, Low. And based on the values selected in A & B, C should get Values High, Med, Low. This is the actual problem. Regards "Bryan Hessey" wrote: Hi Rajula, If you are still having problems, have you considered using the Index function to select matches of A1 & B1. As per the attached, you can allocate values to various combinations, shown as green for A1 and purple for B1 to produce the result you require shown as yellow. The statement is: =IF(ISERROR(OR(MATCH(A1,F$10:F$14,0),MATCH(B1,G$9: K$9,0))),"",INDEX(G$10:K$14,MATCH(A1,F$10:F$14,0), MATCH(B1,G$9:K$9,0))) and the table can be increased to match your current and future needs, as is shown in the coloured zone. Just another possibility. Attached: http://www.excelforum.com/attachment...3&d=1145621998 Hope this helps Rajula Wrote: Hi, It giving all kinds of funny problems. Out of the 3 defined formula, Only onetoseven works. eighttonine formula doesnt work using master. Formula doesnt work for A1, B1 or A2,B2 etc.... Bcos in the defined names the formula is for A1, B1... Regards "Dave Peterson" wrote: Since each one of these conditions is mutually exclusive, you could just concatenate those strings: =IF(AND(A1="H",B1="H"),"H","") &IF(AND(A1="H",B1="M"),"H","") &IF(AND(A1="H",B1="L"),"M","") &IF(AND(A1="M",B1="H"),"M","") &IF(AND(A1="M",B1="M"),"M","") &IF(AND(A1="M",B1="L"),"L","") &IF(AND(A1="L",B1="H"),"M","") (and so forth) Rajula wrote: I used the following link and solved the problem of using more than 7 nested if statements http://www.cpearson.com/excel/nested.htm. I now have 3 defined names (3 formulas). Master, onetoseven and eighttonine. Master is onetoseven and eighttonine combined. I am using Master in cell C1. Now i need to use Master in cell C2, C3, ....etc for hundreds of rows. My problem is 'onetoseven' has IF(AND(A1="H",B1="H"),"H", IF(AND(A1="H",B1="M"),"H", IF(AND(A1="H",B1="L"),"M", IF(AND(A1="M",B1="H"),"M", IF(AND(A1="M",B1="M"),"M", IF(AND(A1="M",B1="L"),"L", IF(AND(A1="L",B1="H"),"M", ))))))) and 'eighttonine' has IF(AND(A1="L",B1="M"),"L", IF(AND(A1="L",B1="L"),"L",)) Now in Cell C2 i need to get IF statements of A2, B2, In C3 i have to get for A3,B3... and so forth. dynamically.. Is there a solution to this. Regards Rajula -- Dave Peterson +-------------------------------------------------------------------+ |Filename: Mif.zip | |Download: http://www.excelforum.com/attachment.php?postid=4673 | +-------------------------------------------------------------------+ -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534670 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=534670 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If workaround - How does it work for other cells dynami
I didn't use any names.
Rajula wrote: Hi, It giving all kinds of funny problems. Out of the 3 defined formula, Only onetoseven works. eighttonine formula doesnt work using master. Formula doesnt work for A1, B1 or A2,B2 etc.... Bcos in the defined names the formula is for A1, B1... Regards "Dave Peterson" wrote: Since each one of these conditions is mutually exclusive, you could just concatenate those strings: =IF(AND(A1="H",B1="H"),"H","") &IF(AND(A1="H",B1="M"),"H","") &IF(AND(A1="H",B1="L"),"M","") &IF(AND(A1="M",B1="H"),"M","") &IF(AND(A1="M",B1="M"),"M","") &IF(AND(A1="M",B1="L"),"L","") &IF(AND(A1="L",B1="H"),"M","") (and so forth) Rajula wrote: I used the following link and solved the problem of using more than 7 nested if statements http://www.cpearson.com/excel/nested.htm. I now have 3 defined names (3 formulas). Master, onetoseven and eighttonine. Master is onetoseven and eighttonine combined. I am using Master in cell C1. Now i need to use Master in cell C2, C3, ....etc for hundreds of rows. My problem is 'onetoseven' has IF(AND(A1="H",B1="H"),"H", IF(AND(A1="H",B1="M"),"H", IF(AND(A1="H",B1="L"),"M", IF(AND(A1="M",B1="H"),"M", IF(AND(A1="M",B1="M"),"M", IF(AND(A1="M",B1="L"),"L", IF(AND(A1="L",B1="H"),"M", ))))))) and 'eighttonine' has IF(AND(A1="L",B1="M"),"L", IF(AND(A1="L",B1="L"),"L",)) Now in Cell C2 i need to get IF statements of A2, B2, In C3 i have to get for A3,B3... and so forth. dynamically.. Is there a solution to this. Regards Rajula -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If workaround - How does it work for other cells dynami
Yeah.. the problem is i cant use more than 7 IF statements. And i have 9 IF statements. "Dave Peterson" wrote: I didn't use any names. Rajula wrote: Hi, It giving all kinds of funny problems. Out of the 3 defined formula, Only onetoseven works. eighttonine formula doesnt work using master. Formula doesnt work for A1, B1 or A2,B2 etc.... Bcos in the defined names the formula is for A1, B1... Regards "Dave Peterson" wrote: Since each one of these conditions is mutually exclusive, you could just concatenate those strings: =IF(AND(A1="H",B1="H"),"H","") &IF(AND(A1="H",B1="M"),"H","") &IF(AND(A1="H",B1="L"),"M","") &IF(AND(A1="M",B1="H"),"M","") &IF(AND(A1="M",B1="M"),"M","") &IF(AND(A1="M",B1="L"),"L","") &IF(AND(A1="L",B1="H"),"M","") (and so forth) Rajula wrote: I used the following link and solved the problem of using more than 7 nested if statements http://www.cpearson.com/excel/nested.htm. I now have 3 defined names (3 formulas). Master, onetoseven and eighttonine. Master is onetoseven and eighttonine combined. I am using Master in cell C1. Now i need to use Master in cell C2, C3, ....etc for hundreds of rows. My problem is 'onetoseven' has IF(AND(A1="H",B1="H"),"H", IF(AND(A1="H",B1="M"),"H", IF(AND(A1="H",B1="L"),"M", IF(AND(A1="M",B1="H"),"M", IF(AND(A1="M",B1="M"),"M", IF(AND(A1="M",B1="L"),"L", IF(AND(A1="L",B1="H"),"M", ))))))) and 'eighttonine' has IF(AND(A1="L",B1="M"),"L", IF(AND(A1="L",B1="L"),"L",)) Now in Cell C2 i need to get IF statements of A2, B2, In C3 i have to get for A3,B3... and so forth. dynamically.. Is there a solution to this. Regards Rajula -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If workaround - How does it work for other cells dynami
You can't nest more than 7 levels. I didn't nest any.
Rajula wrote: Yeah.. the problem is i cant use more than 7 IF statements. And i have 9 IF statements. "Dave Peterson" wrote: I didn't use any names. Rajula wrote: Hi, It giving all kinds of funny problems. Out of the 3 defined formula, Only onetoseven works. eighttonine formula doesnt work using master. Formula doesnt work for A1, B1 or A2,B2 etc.... Bcos in the defined names the formula is for A1, B1... Regards "Dave Peterson" wrote: Since each one of these conditions is mutually exclusive, you could just concatenate those strings: =IF(AND(A1="H",B1="H"),"H","") &IF(AND(A1="H",B1="M"),"H","") &IF(AND(A1="H",B1="L"),"M","") &IF(AND(A1="M",B1="H"),"M","") &IF(AND(A1="M",B1="M"),"M","") &IF(AND(A1="M",B1="L"),"L","") &IF(AND(A1="L",B1="H"),"M","") (and so forth) Rajula wrote: I used the following link and solved the problem of using more than 7 nested if statements http://www.cpearson.com/excel/nested.htm. I now have 3 defined names (3 formulas). Master, onetoseven and eighttonine. Master is onetoseven and eighttonine combined. I am using Master in cell C1. Now i need to use Master in cell C2, C3, ....etc for hundreds of rows. My problem is 'onetoseven' has IF(AND(A1="H",B1="H"),"H", IF(AND(A1="H",B1="M"),"H", IF(AND(A1="H",B1="L"),"M", IF(AND(A1="M",B1="H"),"M", IF(AND(A1="M",B1="M"),"M", IF(AND(A1="M",B1="L"),"L", IF(AND(A1="L",B1="H"),"M", ))))))) and 'eighttonine' has IF(AND(A1="L",B1="M"),"L", IF(AND(A1="L",B1="L"),"L",)) Now in Cell C2 i need to get IF statements of A2, B2, In C3 i have to get for A3,B3... and so forth. dynamically.. Is there a solution to this. Regards Rajula -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If workaround - How does it work for other cells dynamicall
Rajula, Another possibility would be to use a hidden StdData sheet, and assign point values to High, Medium & Low (i.e., High = 3, Med = 2, Low = 1). In your corresponding StdData sheet cells, you could determine the point value with IFs, LOOKUP (etc..many choices of tools). Your StdData C cell could then have a very simple formula to determine the value threshold (val 4 = rslt1, 3 val < 2 = rslt2, etc). This should net you fewer possibilities. You could then post the col C values onto your presentation sheet. -- BruceP ------------------------------------------------------------------------ BruceP's Profile: http://www.excelforum.com/member.php...o&userid=33653 View this thread: http://www.excelforum.com/showthread...hreadid=534670 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If workaround - How does it work for other cells dynami
Hey Dave, i should thankyou for this..
Its works... GREAT. "Dave Peterson" wrote: I didn't use any names. Rajula wrote: Hi, It giving all kinds of funny problems. Out of the 3 defined formula, Only onetoseven works. eighttonine formula doesnt work using master. Formula doesnt work for A1, B1 or A2,B2 etc.... Bcos in the defined names the formula is for A1, B1... Regards "Dave Peterson" wrote: Since each one of these conditions is mutually exclusive, you could just concatenate those strings: =IF(AND(A1="H",B1="H"),"H","") &IF(AND(A1="H",B1="M"),"H","") &IF(AND(A1="H",B1="L"),"M","") &IF(AND(A1="M",B1="H"),"M","") &IF(AND(A1="M",B1="M"),"M","") &IF(AND(A1="M",B1="L"),"L","") &IF(AND(A1="L",B1="H"),"M","") (and so forth) Rajula wrote: I used the following link and solved the problem of using more than 7 nested if statements http://www.cpearson.com/excel/nested.htm. I now have 3 defined names (3 formulas). Master, onetoseven and eighttonine. Master is onetoseven and eighttonine combined. I am using Master in cell C1. Now i need to use Master in cell C2, C3, ....etc for hundreds of rows. My problem is 'onetoseven' has IF(AND(A1="H",B1="H"),"H", IF(AND(A1="H",B1="M"),"H", IF(AND(A1="H",B1="L"),"M", IF(AND(A1="M",B1="H"),"M", IF(AND(A1="M",B1="M"),"M", IF(AND(A1="M",B1="L"),"L", IF(AND(A1="L",B1="H"),"M", ))))))) and 'eighttonine' has IF(AND(A1="L",B1="M"),"L", IF(AND(A1="L",B1="L"),"L",)) Now in Cell C2 i need to get IF statements of A2, B2, In C3 i have to get for A3,B3... and so forth. dynamically.. Is there a solution to this. Regards Rajula -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nested If workaround - How does it work for other cells dynami
You could add more conditions to this a little easier:
=IF((LEN(A1)*LEN(B1))<1,"", VLOOKUP(A1&B1,{"HH","H"; "HM","H"; "HL","M"; "MH","M"; "MM","M"; "ML","L"; "LH","M"},2,FALSE)) (all one cell) Rajula wrote: Hey Dave, i should thankyou for this.. Its works... GREAT. "Dave Peterson" wrote: I didn't use any names. Rajula wrote: Hi, It giving all kinds of funny problems. Out of the 3 defined formula, Only onetoseven works. eighttonine formula doesnt work using master. Formula doesnt work for A1, B1 or A2,B2 etc.... Bcos in the defined names the formula is for A1, B1... Regards "Dave Peterson" wrote: Since each one of these conditions is mutually exclusive, you could just concatenate those strings: =IF(AND(A1="H",B1="H"),"H","") &IF(AND(A1="H",B1="M"),"H","") &IF(AND(A1="H",B1="L"),"M","") &IF(AND(A1="M",B1="H"),"M","") &IF(AND(A1="M",B1="M"),"M","") &IF(AND(A1="M",B1="L"),"L","") &IF(AND(A1="L",B1="H"),"M","") (and so forth) Rajula wrote: I used the following link and solved the problem of using more than 7 nested if statements http://www.cpearson.com/excel/nested.htm. I now have 3 defined names (3 formulas). Master, onetoseven and eighttonine. Master is onetoseven and eighttonine combined. I am using Master in cell C1. Now i need to use Master in cell C2, C3, ....etc for hundreds of rows. My problem is 'onetoseven' has IF(AND(A1="H",B1="H"),"H", IF(AND(A1="H",B1="M"),"H", IF(AND(A1="H",B1="L"),"M", IF(AND(A1="M",B1="H"),"M", IF(AND(A1="M",B1="M"),"M", IF(AND(A1="M",B1="L"),"L", IF(AND(A1="L",B1="H"),"M", ))))))) and 'eighttonine' has IF(AND(A1="L",B1="M"),"L", IF(AND(A1="L",B1="L"),"L",)) Now in Cell C2 i need to get IF statements of A2, B2, In C3 i have to get for A3,B3... and so forth. dynamically.. Is there a solution to this. Regards Rajula -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| merge cells button doesn't work | Excel Worksheet Functions | |||
| Why doesn't this nested IF statement work? | Excel Worksheet Functions | |||
| Nested subtotals don't work properly after applying recommendation | Excel Worksheet Functions | |||
| Nested IF - Work Around for 7 nest Max | Excel Worksheet Functions | |||
| Help adding text values | Excel Worksheet Functions | |||
Hybrid Mode
