compute excel average score based on color and custom score

Akshaykum

New member
Joined
Feb 22, 2022
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2016
I am trying to compute average of each row based on color scores.
each color has a score. ex: red color = 0, green color = 90 and Tacao = 50 (please don't use the score from excel dropdown values)
average for 1st row is 46 and 2nd row is 41.25. File can be downloaded from https://github.com/DeepSpace2/StyleFrame/issues/112
 
Last edited by a moderator:

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,156
Reaction score
4
Points
38
Excel Version(s)
365
In cell BH5 (still part of your table):
Code:
=IFERROR(SUM(INDEX({0;50;90},MATCH(Table22[@[Column1]:[Column16]],{0;33;66})))/COUNT(Table22[@[Column1]:[Column16]]),"")
This does not look at the colours, instead I've used similar rules as your conditional formatting using the actual values in each cell. Of course, if you use different conditional formatting for different parts of your table this will no longer work.
 

Attachments

  • ExcelGuru11460DUMMY_DATA_TEST.xlsx
    125.1 KB · Views: 2
Top