Nested If workaround - How does it work for other cells dynamicall

[Guest]

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]

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)

 

[Guest]

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

 

[Bryan Hessey]

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

 

[Bryan Hessey]

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.php?attachmentid=4673&d=1145621998

Hope this helps

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

+-------------------------------------------------------------------+
|Filename: Mif.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4673 |
+-------------------------------------------------------------------+

 

[Dave Peterson]

I didn't use any names.

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

 

[Guest]

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

 

[Guest]

Yeah.. the problem is i cant use more than 7 IF statements. And i have 9 IF
statements.

 

[Dave Peterson]

You can't nest more than 7 levels. I didn't nest any.

Yeah.. the problem is i cant use more than 7 IF statements. And i have 9 IF
statements.

 

[Guest]

Hey Dave, i should thankyou for this..
Its works... GREAT.

 

[BruceP]

Rajula,

Another possibility would be to use a hidden StdData sheet, and assig
point values to High, Medium & Low (i.e., High = 3, Med = 2, Low = 1).
In your corresponding StdData sheet cells, you could determine the poin
value with IFs, LOOKUP (etc..many choices of tools). Your StdData
cell could then have a very simple formula to determine the valu
threshold (val > 4 = rslt1, 3 > val < 2 = rslt2, etc). This shoul
net you fewer possibilities. You could then post the col C values ont
your presentation sheet

 

[Bryan Hessey]

Rajula,

Irrespective of how you get the contents into cells A or B, th
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 fo
where A value crosses with B value, and this will be selected when A
and B1 are set.

--

 

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

Hey Dave, i should thankyou for this..
Its works... GREAT.