CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Amandip Singh
Amandip Singh

Reputation: 55

Excel - If range of cells

I am trying to look at the skill ratings displayed in columns B:G and RETURN the relevant value listed below into column A.

enter image description here

The different permutations I have to cover are below:

All 3's RETURN 3
All 2's RETURN 2
All 1's RETURN 1
Mix of 3's and 2's RETURN 2
Mix of 2's and 1's RETURN 1
Mix of 3's and 1's RETURN 2
Mix of 3's, 2's and 1's return 2

I used the formula below to get as close as possible to the end goal but I soon realised that the data would be inaccurate at some points.

=IF(SUM(B2:G2)=18,3,IF(SUM(B2:G2)=12,2,IF(SUM(B2:G2)=6,1,IF(SUM(B2:G2)<12,1,IF(SUM(B2:G2)<18,2)))))

Thank you in advance for any help you may offer!

Upvotes: 1

Views: 63

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34210

How about this

=FLOOR(MEDIAN(MIN(B2:G2),MAX(B2:G2)),1)

enter image description here

Upvotes: 3

Scott Craner
Scott Craner

Reputation: 152505

This should satisfy all rules:

=IF(AND(AVERAGE(B2:G2)<=2,COUNTIF(B2:G2,3)>0),2,INT(AVERAGE(B2:G2)))

enter image description here

Upvotes: 2

Related Questions