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

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

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
>

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

Originally Posted by Rajula

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
>

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

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
>
>

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
>

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

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.

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.

--

Originally Posted by Rajula

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
>
>

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
>

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