If/Then formula that calculates results based on combination of two cells

So, I have a document control spreadsheet I'm trying to work up. The idea is to assess risk of document completion on time based on start date, due date and current status. I have these fields:

Start Date - date document started
Current Date - current date
Due Date - date document should be complete
Status - document completion status. This is a drop-down list (data validation list from another worksheet) with the following options:

Assigned
Started
Draft Complete; out for Review by PDC
1st Review Complete; Out for Review by AM
Undergoing Revision
Revisions Complete
Completed



Now, what I need is this. In a column labeled "Risk", a formula should be in place that looks at the difference in due/current date AND the current document status to determine risk of not getting it done on time. Example:

If status is anything other than Undergoing Revision, Revisions Complete, or Completed, AND the difference in due/current date is 3 days or less, then the risk is medium. Make sense?

Thanks!

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Okay, here you go...

As a side question, is there any way to make sure that when someone adds a new document (creats a new row) that the data validation/formuals will apply without manually dragging them in? Trying to develop something that's fairly "idiot proof" for people VERY unfamiliar with Excel.

Try this: return 1 for Medium.

Great, thanks! That's ideal!