Embedded If Formula Help

[Excelnoob69]

Hi guys,

For a bit of background, I am trying to create an imbedded If formula. The scenario is a student who has to submit multiple deliverables. I am hoping to track whether they are on track using a formula. Here are the parameters:

- If the deliverables have been submitted, then it's On Track

- If the deliverables have not been submitted but is due within the next 20 days, then it has Challenges Identified

- If the deliverables have not been submitted and it's past the due date, then it's Not on Track

- If the deliverables has not been submitted but it's due after 20 days, it shouldn't affect the status until it's within 20 days

I think it's easy enough if we have a status cell for each deliverable, but I am hoping to create 1 big formula, so if all deliverables have been submitted, it'll be On Track. But if one deliverable has not been submitted within 20 days, it would ideally say 'Challenges'.

Also If I have multiple students (some with 5 deliverables, some with 10 and some with 2), is there any way to create a 'one size fits all' formula, or would I have to modify each formula?

I had asked help on this forum before and was given this formula for a single cell, but now I am hoping to combine everything: =IF(E2="Yes","On Track",IF(TODAY()<D2,"",IF((TODAY()-D2)<20,"Challenges2","Not on Track")))

[#2]

@Excelnoob69 

You've had over 60 views and no replies. I suspect that the issue here is that more definition is required, so let me first ask for some more clarification and suggest that, if at all possible, you give us access to the actual spreadsheet from which your image was taken.

Clarification: you go back and forth between referring to the deliverable(s) in the plural and in the singular, so let me ask you to clarify those various sentences. For example here's just one of your descriptive sentences, red being plural, green suggesting single/individual: "- If the deliverables have not been submitted but is due within the next 20 days, then it has Challenges Identified"

1. Are you looking for a status on each individual deliverable? Is that all you're seeking?
2. OR, are you also looking for some sort of collective result for each student with regard to his or her overall status, regardless of how many deliverables that individual student has?

If you can, please attach the sample spreadsheet to your reply. If that's not possible, grant us access to a copy in OneDrive, GoogleDrive, or some other comparable cloud service. That way we could get a more complete grasp of what you're working with. The image is helpful, but an actual working spreadsheet is far far better.