MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Nesting problem, maybe?


Posted by Harvey on October 26, 2001 8:59 AM

Okay, I'm new to this board, so please forgive me if I'm asking a stupid question.

I need to fill a cell based on two fields. I tried nesting the criteria, but it's too long (I think). Anyway, here's what I have.
Column One (Impact) Values of High, Med or Low
Column Two (Probability) values of High Med or Low
Column Three (Priority) needs to return a value of 1-8 based on the prior two columns.

High/High = 1
Med/High = 2
High/Med = 3
Med/Med = 4
Low/High = 5
Low/Med = 6
High/Low = 7
Med/Low = 8
Low/Low is not a valid combination (no one will put those values in, so it's a moot point)

I tried using a nested If [EX. =IF(AND(E40="Med",F40="Low"),8,(IF(AND(E40="Med",F40="High"),2,(IF(AND(E40="High",F40="Med"),3,(IF(AND(E40="Med",F40="Med"),4,(IF(AND(E40="Low",F40="High"),4,(IF(AND(E40="Low",F40="High"),5,(IF(AND(E40="High",F40="Low"),7," ")))))))))))))]formula and it worked great until I got too many nested section, then the formula became invalid. I tried several things and it looks like it's either the length, or there's a limit to the number of nested functions you can have. Even if I could get the nestings to work, it's ugly and I'm pretty sure there's a much more elegant way to get the return I need, I just have no idea what it it.

I thought maybe making a short listings of the values in spare cells on another sheet or something, but I don't know how to make the cells return the value I want.

Sorry, now I'm just babbling. If someone could please take a moment and tell me what to do, or at least which approach to take, I'd greatly appreciate it.

Thanks

Harvey Davis


Posted by lenze on October 26, 2001 9:06 AM

You might try using VLOOKUP along with the CONCATENATE Function. Something like =VLOOKUP(CONCATENATE(A1,B1),SourceTable,2,FALSE). In your source table, put HighHigh,HighMed,etc. In the first column and the value in the second column.

HTH

Posted by Harvey on October 26, 2001 9:10 AM

Thanks! That sounds like a MUCH more elegant solution. I'm never used that function, but I'll give it a shot.

Thanks
Harvey

Posted by Mark W. on October 26, 2001 9:17 AM

A MATCH worsheet function will suffice...

Posted by Harvey on October 26, 2001 9:21 AM

Re: A MATCH worsheet function will suffice...

Okay, I can see how that would match the cells up, but how would that return the priority value?

Harvey

Posted by Harvey on October 26, 2001 9:24 AM

Ignore that last, I think I see what you're saying

Thanks!
Harvey


Posted by Mark W. on October 26, 2001 9:28 AM

Just to insure comprehension...

Match returns the index from the array (its 2nd
argument). So if Impact and Probability are "High"
the a 1 is returned. If they're "Med" and "High"
respectively than a 2 is returned... etc. !


Posted by Harvey on October 26, 2001 9:31 AM

Right! I just wasn't reading the MATCH definition correctly


Posted by Harvey on October 26, 2001 9:32 AM

Right! I just wasn't reading the MATCH definition correctly

Once I did, it made sense. Thanks Mark!

Harvey


Posted by Harvey on October 26, 2001 9:39 AM

Thanks Mark, it works fine. (BTW how do you keep it from showing #N/A if the other two cells are blank?)

Posted by Mark W. on October 26, 2001 9:46 AM

Re: Thanks Mark, it works fine. (BTW how do you keep it from showing #N/A if the other two cells are blank?)

You could use...

=IF(AND(ISTEXT(Impact),ISTEXT(Probability)),MATCH(Impact&Probability,{"HighHigh";"MedHigh";"HighMed";"MedMed";"LowHigh";"LowMed";"HighLow";"MedLow"},0),"") : =MATCH(Impact&Probability,{"HighHigh";"MedHigh";"HighMed";"MedMed";"LowHigh";"LowMed";"HighLow";"MedLow"},0)

Posted by Harvey on October 26, 2001 9:57 AM

Beauty! Thanks again.

=IF(AND(ISTEXT(Impact),ISTEXT(Probability)),MATCH(Impact&Probability,{"HighHigh";"MedHigh";"HighMed";"MedMed";"LowHigh";"LowMed";"HighLow";"MedLow"},0),"")