Returning project status based on dates in multiple cells using a nested IF function


I am trying to create a cell that shows whether a project is on track, late or ahead of schedule using multiple dates derived from multiple tasks within the project.

For example, if the cell is filled then it will check to see if the date is equal to, greater than or less than the planned finish date to show if it is on track, late or ahead. If the cell is blank however, then I want it to check the previous task/cells to check if that is filled and if so return the correct answer. If that cell is also blank then it checks the previous cell and so on. 

I beleive that this is the best way to do this as it might be too complicated to check against the first task using the criteria and then before it offers the answer checks the next task to check it isn’t filled; if it isn’t it would keep the answer from the previous cell ,if it is filled then it applys to that cell to return either on track, ahead or late.

I have tried to use a nested IF function to complete this as shown below. I am aware that there may be too many nested IFs but even so the formula only works on the first lot of cells even if it is blank, rather than moving on to the next cell to check. Excel accepts the formula but it doesn’t work for some reason.

=IF(F36=F35, “ON TRACK”, IF(F36>F35, “LATE”, IF(F36<F35, “AHEAD”, IF(F36=””, IF(F34=F33, “ON TRACK”, IF(F34>F33, “LATE”, IF(F34<F33, “AHEAD”, IF(F34=””,IF(F32=F31, “ON TRACK”, IF(F32>F31, “LATE”, IF(F32<F31, “AHEAD”, IF(F32=””,IF(F30=F29, “ON TRACK”, IF(F30>F29, “LATE”, IF(F30<F29, “AHEAD”, IF(F30=””,IF(F28=F27, “ON TRACK”, IF(F28>F27, “LATE”, IF(F28<F27, “AHEAD”, IF(F28=””,IF(F26=F25, “ON TRACK”, IF(F26>F25, “LATE”, IF(F26<F25, “AHEAD”, IF(F26=””,IF(F24=F23, “ON TRACK”, IF(F24>F23, “LATE”, IF(F24<F23, “AHEAD”, IF(F24=””,IF(F22=F21, “ON TRACK”, IF(F22>F21, “LATE”, IF(F22<F21, “AHEAD”, IF(F22=””,IF(F20=F19, “ON TRACK”, IF(F20>F19, “LATE”, IF(F20<F19, “AHEAD”, IF(F20=””,””))))))))))))))))))))))))))))))))))))

I am also aware that using the IFS function might be useful but I am unsure of how this works.

I am using Excel 2016 and Windows.

Any help would be greatly appreciated! Smile And apologies if this has been asked before but I cannot find a solution anywhere. 

By: Callum

Leave a Reply

Your email address will not be published. Required fields are marked *