CODEWITHSUNDEEP
excelif-statementexcel-formula
Hershey672
Hershey672

Reputation: 37

Excel - formula for If/Then conditions with negative numbers

I'm working on a file that will measure test results and identify gaps for required knowledge competency vs current knowledge competency. I have 5-6 competencies to measure for each person. The lowest gap would be -4 while the highest is 4.

Conditions (IF) Rating (THEN)
All gaps have negative values Does not meet requirements
Has 1-2 negative values but with 1-2 zero gaps Meets some requirements
All gaps are zero Meets all requirements
1-2 gaps are positive, can have zero Exceeds some requirements
All gaps are positive and have no zero Exceeds all requirements

For example,

Subjects Gaps Remarks
Competency 1 2 2 pts higher than expected
Competency 2 -1 Less than expected
Competency 3 -1 Less than expected
Competency 4 -1 Less than expected
Competency 5 0 expected
Competency 6 1 1 pt higher than expected
Total Rating Meets some requirements

I've tried dissecting step-by-step and incorporating Index/Match/Mode formulas as well but I can't seem to make it work. I really need some help with this! Thank you in advance!

With formula on address bar

Values only; without formula

Upvotes: 0

Views: 1322

Answers (1)

BigBen
BigBen

Reputation: 49998

Assuming the gaps are in B2:B7, I believe this follows your logic:

=IF(COUNTIF(B2:B7,">=0")=0,"Does not meet",
 IF(COUNTIF(B2:B7,0)=COUNT(B2:B7),"Meets all",
 IF(COUNTIF(B2:B7,">0")=COUNT(B2:B7),"Exceeds all",
 IF(COUNTIF(B2:B7,"<0")>0,"Meets some",
 "Exceeds some"))))
 &" requirements"

Upvotes: 2

Related Questions