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.