Automatically allocate priority number based on date & text field

[Talsar]

Hi, this has been driving me daft all day and would really appreciate some help.

As you can see below, I want to have column A automatically allocate a priority number (1, 2, 3, 4 etc), based around 3 formula's;

C Has three values = HIGH, MED, LOW

Where C = HIGH is allocated a higher priority then MED, then LOW

B is deadline dates i was thinking of

If B2>today() & C2="HIGH" Priority = 1
If B2=today() & C2="HIGH" Priority = 2
IF (B2+14)<TODAY() & C2="HIGH" Priority = 3
If B2>today() & C2="MED" Priority = 4
If B2=today() & C2="MED" Priority = 5
IF (B2+14)<TODAY() & C2="MED" Priority = 6
If B2>today() & C2="LOW" Priority = 1
If B2=today() & C2="LOW" Priority = 2
IF (B2+14)<TODAY() & C2="LOW" Priority = 3

A to be automatic
B will always contain a date
C will have one of three values - HIGH, MED, LOW

 

[AlphaFrog]

If B2>today() & C2="HIGH" Priority = 1
If B2=today() & C2="HIGH" Priority = 2
IF (B2+14) < TODAY() & C2="HIGH" Priority = 3
If B2>today() & C2="MED" Priority = 4
If B2=today() & C2="MED" Priority = 5
IF (B2+14) < TODAY() & C2="MED" Priority = 6
If B2>today() & C2="LOW" Priority = 1
If B2=today() & C2="LOW" Priority = 2
IF (B2+14) < TODAY() & C2="LOW" Priority = 3

You have the "LOW" values in C with a priority of 1, 2, and 3. The same as the "HIGH" values in C. Is that what you really want? Or should they be 7, 8, and 9 ?

 

[Andrew Poulsom]

Welcome to the Board.

Maybe try:

=IF(B2>TODAY(),IF(OR(C2="HIGH",C2="LOW"),1,4),IF(B2=TODAY(),IF(OR(C2="HIGH",C2="LOW"),2,5),IF(OR(C2="HIGH",C2="LOW"),3,6)))

 

[milesUK]

nest the If statements like below but you can double up some by using AND. In cell A2 place this formula:
=IF(AND( B2>TODAY(), C2="HIGH"), 1,IF(AND(B2=TODAY(), C2="HIGH"),2,IF(AND(B2>TODAY(), C2="MED"), 4,IF(AND( B2=TODAY(), C2="MED"), 5,IF(AND( B2>TODAY(), C2="LOW"), 1,IF(AND( B2=TODAY(), C2="LOW"),2,"cannot Determine Priority"))))))

a few erronious bits threw me though e.g IF (B2+14) !!!

 

[baitmaster]

welcome to the baord

I'm not clear on your requirement, please can you check and reconfirm

you mention priority numbers based on 3 formulae, but I see more or less than this depending how I interpret, but never 3

Your suggestions for your priorities don't seem to work either:
formulae 1 and 2 suggest that the higher the priority number, the more important something is, but formulae 3 and 4 suggest the opposite - although there also seems to be a typo, or something missing (have you used "<" and ">"? These don't show in certain circumstances

Is your problem about how to write in Excel the formulae you already know, or the how to create the actual formulae you need, before considering writing in Excel?

It looks to me like a sequence of nested IF statements will handle this, but that your problem is in the formula itself. The sections that appear to missing will probably work with the inclusion of AND(condition 1,condition 2)

 

[Andrew Poulsom]

Some of conditions in the first post are missing - the less than signs have been interpreted as HTML.

The complete list of conditions is in the quote in AlphaFrog's post.

 

[AlphaFrog]

If B2>today() & C2="HIGH" Priority = 1
If B2=today() & C2="HIGH" Priority = 2
IF (B2+14) < TODAY() & C2="HIGH" Priority = 3
If B2>today() & C2="MED" Priority = 4
If B2=today() & C2="MED" Priority = 5
IF (B2+14) < TODAY() & C2="MED" Priority = 6
If B2>today() & C2="LOW" Priority = 7
If B2=today() & C2="LOW" Priority = 8
IF (B2+14) < TODAY() & C2="LOW" Priority = 9

I went with the LOW priority being 7, 8, and 9

=IF(B2+14 < TODAY(),"", 1 + (B2=TODAY()) + AND(B2+14 >=TODAY(),B2< TODAY())*2 + (C2="MED")*3 + (C2="LOW")*6)

 

[Talsar]

Wow thanks for the fast reply

AlphaDog/ MilesUK and Andrew you all nailed it - it is working a treat

Alpha yer i meant to put 6, 7, 8 for the low values (i was cutting and pasting editing lol)

Many thanks guys! another job out of my in tray

 

[Talsar]

Alright this is my second post on this topic, I was not clear on my first one and it lead to a solution that although it worked, it did not do as I had hoped.
<o> </o>
I am new to excel and this has been driving me nuts all day. I am not going to try and write excel formula as I am not too great on it, I will write how I think it should work.
<o> </o>
The excel document I am wanting help on is what i will call a ‘Task Tracker’, every morning I want it to open up and put my daily tasks into order by priority (Column A).
<o> </o>
Priority is determined by two factors – Date & Value (how important to my organisation);
<o> </o>
Date (Column B);
<o> </o>
If date 14 days in the future it is not urgent
If date 7 days in the future it is now required
If date less then today date it is now urgent
<o> </o>
Value (Column C)
<o> </o>
If HIGH – it is a High value task
If MED – it is a Med value task
If LOW – it is a Low value task
<o> </o>
<o> </o>
So now Column A is to work out automatically depending on the values of column B & C
<o> </o>
(This is where I wont try to write excel formula as I messed it up before, I will just type it in English)
<o> </o>
IF B date is less than today date and C value is HIGH then task is allocated priority 1
IF B date is 7 days in the future than today date and C value is HIGH then task is allocated priority 2
IF B date is 14 days in the future than today date and C value is HIGH then task is allocated priority 6
IF B date is less than today date and C value is MED then task is allocated priority 3
IF B date is 7 days in the future than today date and C value is MED then task is allocated priority 4
IF B date is 14 days in the future than today date and C value is MED then task is allocated priority 7
IF B date is less than today date and C value is LOW then task is allocated priority 5
IF B date is 7 days in the future than today date and C value is LOW then task is allocated priority 8
IF B date is 14 days in the future than today date and C value is LOW then task is allocated priority 9
<o> </o>
Hope this makes sense this time hard to translate what I wanted when not too good as excel formula.

 

[Andrew Poulsom]

It would be better to stick with your original thread:

http://www.mrexcel.com/forum/showthread.php?t=469135

where you said your problem was resolved.