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),"")
