Hello all!
I am working on an excel project that requires a complex series of nested ifs. There might be a better way to do this, so if you have any thoughts, let me know!
Essentially, it tracks performance of a specific individual over the span of three months. Each individual contributes X% on a weekly basis. The goal is to hit 90% or above every week. If that person consistently hits 90%, no changes need to be applied. If a person is below 90% for two consecutive weeks, they will be designated as "A". At that point, if they hit 90% or above for two consecutive weeks, they are back to "O" and the designations start from point O. However, if they are below 90% for two more consecutive weeks, they are designated as "B". Similarly, if they are 90% or above for the next two weeks, they are back to "O" and the designation start from point O. If they are a "B" and are trending in a positive direction after a week, they can stay at "B" until they hit 90% for two more weeks. However, if they are trending in a negative direction for 1 week and are below 90% for two weeks in a row, they are marked "C".
If you are a "C" and you hit 90% for two weeks in a row, you are back to "O". However, if you are below 90% within the next two week span, you are back to a "C" rather than starting off at O. If you are below 90% anytime over the next 12 weeks, you are back to a "B" rather than starting off at O.
One other issue:
If you are an "A" three times within a 12 week span, you will be marked as "B"
I have attached a sample document that might work. If you could please provide your thoughts on how to best set up the logic, I would really appreciate it! Thanks in advance!
Bookmarks