Formula Calculation based on Different Values

Good afternoon,

I have a spreadsheet that I am using for tracking Issues and Action Items. My goal is to the have the Priority Field auto calculate a value based on if the item is an issue or an action. My objective is to have the Priority for and action be time driven and the priority value for an issue be driven by a value determined by Impact X Urgency.

I have a Formula that works for each individually, but need to combine them so that the calculation looks at the value of the "Type" field and then chooses the appropriate calculation based on if the value is "Action" or "Issue"

The formula for Actions (Time Based) is =IF(L2="","",LOOKUP(L2-TODAY(),{-120,5,10,15,30},{"Critical","Critical","High","Medium","Low"}))
The Formula for Issue (Value of Impact*Urgency) is =IF(H3=0,"",IF(H3<=7,"Low",IF(H3<=14,"Medium",IF(H3<=19,"High",IF(H3>=20,"Critical")))))

How do I combine them so they use the correct calculation based on the Value displayed in a different cell ("type")?

I have attached a file for review. Please let me know if further clarification is needed.

Thank you in advance for your assistance.

Originally Posted by BMV

примерно так

Please Login or Register  to view this content.

Thank you for this.

However, when I plug it into the spreadsheet I receive an generic error. "Not trying to type a formula? When the first character is an equal(=) or minus(-)sign, Excel thinks it is a formula:
You type: =1+1, cell shows 2
Toget around this, type an apostrophe (') first:

Did you try it in my attached file? Any other thoughts would be appreciated.

Thank you

Good evening,

Does anyone have any other thoughts on how to solve this? The suggested solution did not work as written. If any one has any ideas I would greatly appreciate it.

Thank you

The file in the attachment.

Originally Posted by BMV

The file in the attachment.

Thank you for this. I appreciate your assistance and timely response.