Priority Column based on several factors

0

I have several columns and i need the priority column to state wether the priority is high, Medium High, Medium, Low based on criteria in several of the other columns.

High

New members

Members with score 5 or higher

Or members that have score less than 5 but are high risk and have a date blank or not in 2019

Medium High

Score 2-4

No 2019 date

Medium

Score 0-1

No 2019 date

Low

score 0

2019 date

Here are the columns with some data

Date Score Risk Status Priority 10/13/2018 9 Y Retained   2/1/2019 4 N NEW     3 Y Retained   1/14/2017 6 N Retained   1/5/2016 12 Y Retained     1 N Retained   8/25/2018 0 N NEW  

Answer
Discuss

Discussion

This isn't difficult but it needs a workbook with some data. You can attach a workbook by editing your question.
Variatus (rep: 2613) Feb 22, '19 at 6:32 am
Ok i added a small sample if you could help me that would be amazing
cramos1 Mar 1, '19 at 9:30 pm
Add to Discussion

Answers

0

Please try this formula in E2 of your example and copy down.

=IF(YEAR($A2)=2019,"Low",IF(OR($C2="y",$D2="new"),"High",INDEX({"Low","Medium","Medium High","High"},MATCH($B2,{0,1,2,5},1))))

The following logic is applied.

  1. If the date's year = 2019 the priority is "Low"
  2. If the (Status = New) or (Risk = Y), the priority is "High"
  3. If neither of the above applies, priority is decided by Score:
    =>5 = "High"
    =>2 = "Medium High"
    =>1 = "Medium"
    =>0   ="Low"
    This schedule is laid out in the MATCH part of the formula, i.e.MATCH($B2,{0,1,2,5},1)) and may be modified there. It isn't possible to assign 0 score to both "Medium" and "Low".

Here is a variation on the above which arrives at the same result in a different way.

=INDEX({"Low","Medium","Medium High","High"},MATCH(IF(YEAR($A2)=2019,0,IF(OR($C2="y",$D2="new"),100,$B2)),{0,1,2,5},1))

In this variation the priority is purely decided by the score but the score itself is modified.

  1. If the date's year = 2019 the score = 0
  2. If the (Status = New) or (Risk = Y), the score = 100
  3. If neither of the above applies, Score is taken from column B
Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login