CODEWITHSUNDEEP
if-statementoptimizationexcel-formulaexcel-2010formula
JustinS
JustinS

Reputation: 3

Excel Formula IF Statements Guidance

I am trying to create a spread sheet to track employees skill in certain tasks. Each task is given its own sheet and then broken down into more detailed minor tasks.

Each task is then linked back to a summary page where an overview is given of all employees and all tasks. This info is auto filled based on the info used on other pages.

I currently have it set up as "Yes" or "No" the employee can preform this task.

Example:

Employee   Task 1   Task 2   Task 3   Task 4
Steve           Yes       Yes         No         Yes
Joe              Yes       Yes         Yes       Yes
Mark             No        No          Yes        Yes

If all minor tasks per page are "Yes" then on the summary page under that major task it will display "Yes". If all but one are listed as "Yes", but one is listed as "No" then a "No" will be displayed on the summary page on that task.

Here is the formula I used:

=IF(AND(C3="yes",D3="yes",E3="yes",F3="yes",G3="yes",H3="yes",I3="yes",J3="yes",K3="yes",L3="yes",M3="Yes"),"Yes","No")

Here is where is get stuck....

I am now needing it to have 3 options: Yes, No and Needs Impovement.

My question is how do I get excel to display 1 of 3 options? If the minor tasks are all listed as "Yes" I would still like the summary page to read "Yes", but if one minor task is listed as "Needs Improvement" then I would like it to display "Needs Improvement" and last is one is listed as "No", even if another is listed as "Needs Improvement", then have it display "No".

It is really hard to explain my issue without being able to show examples. If you would like to see the workbook please email me.

Upvotes: 0

Views: 221

Answers (1)

xQbert
xQbert

Reputation: 35323

Something like this may work. As you have multiple sheets you need to adjust the ranges and the counts. I used 5 possible subtask values; you can adjust accordingly.

What it does is count the number of subtasks of yes. If all of them are yes (5) in my example it displays yes. If a single entry is no, then it returns no, otherwise it returns needs improvement.

enter image description here

Upvotes: 1

Related Questions