Formula for Priority cells with Impact

Maty

Hi, I am trying to build the correct formula to automatically populate the priority based on the following conditions. For example: If impact is High, and effort is low, auto input 30 days in the Bucket column and auto set priority of 1..etc. I am getting an error message.

Impact	Effort	Bucket	Priority
High	Low	30 Days	1
High	Med	60 Days	2
High	High	>90 Days	4
Med	Low	60 Days	3
Med	Med	>90 Days	5

prime_nathaniel

@Maty

You need to clarify your use cases and expected results. Assuming there is a Low Impact as well. You have 3x3 results of 2 items. If there is no low then its 2x3. From your table it seems maybe there is no effort high for a impact medium?

Bucket -
High, High =
High, Med =
High, Low =
Med, High =
Med, Med =
Med, Low =
Low, High =
Low, Med =
Low, Low =

You also have not given sufficient requirements for your priority level

I will go off your table as it is written but it seems incomplete.

You simply nest your if then formulas by however many cases you need if you have 5 use cases with no fall through default then you need 4 if thens (because the final else is case 5). If you have 5 explicit use cases and a default fall through of "" or "error" then you need 5 if thens.

You write that out as

if( aCase, aResult, if( bCase, bResult, if( cCase, cResult, if( dCase, dResult, eResult))))

You can now plug all your use cases and results in one by one. Again assuming you wrote inclusively ALL use cases in your table. Your use cases are

aCase = and([Impact]@row="High",[Effort]@row="High")
aResult = "30 Days"

Repeat this for all your use cases and plug back into

if( aCase, aResult, if( bCase, bResult, if( cCase, cResult, if( dCase, dResult, eResult))))

Now write the same thing with aResult for priority

Convert to cell formula

done

Maty

I don't follow. If Impact =Med, then Effort =high, and Bucket = >90days then priority should be 6.

This is the formula I have, what am I doing wrong?

=IF(AND(Impact@row = "High", Effort@row = "Low", Bucket@row = "30 days"), 1, =IF(AND(Impact@row = "High", Effort@row = "Medium", Bucket@row = "60 days"), 2, =IF(AND(Impact@row = "High", Effort@row = "High", Bucket@row = ">90 days"), 4, =IF(AND(Impact@row = "Medium", Effort@row = "Low", Bucket@row = "60 days"), 3, =IF(AND(Impact@row = "Medium", Effort@row = "Medium", Bucket@row = ">90 days"), 5, =IF(AND(Impact@row = "Medium", Effort@row = "High", Bucket@row = ">90 days"), 6, ""))))))

Maty

I figured it out. Thank you.

Paul Newcome

If you have that table in another sheet, you can use an INDEX/COLLECT to reference it and really cut down on the overall formula length as well as add some flexibility if you need to add, remove, or adjust any of the variables.

=INDEX(COLLECT({Column To Pull From}, {1st Criteria Column}, 1st criteria, {2nd Criteria Column}, 2nd criteria, {3rd Criteria Column}, 3rd criteria), 1)