Need Help with Scoring Formula Based on Cell Color

%3CLINGO-SUB%20id%3D%22lingo-sub-1097747%22%20slang%3D%22en-US%22%3ENeed%20Help%20with%20Scoring%20Formula%20Based%20on%20Cell%20Color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1097747%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20hit%20a%20road%20block%20on%20a%20large%20spreadsheet%20(300%2B%20mb)%20that%20I've%20been%20working%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20used%20conditional%20formatting%20to%20color%20code%20cells%20in%2015-20%20columns.%20I%20want%20to%20create%20a%20formula%20that%20will%20generate%20a%20score%20for%20that%20row%2C%20based%20on%20values%20that%20I%20assign%20to%20different%20colors.%20Some%20of%20the%20scores%20for%20the%20same%20color%20would%20vary%20based%20on%20the%20column.%20For%20example%2C%20a%20blue%20cell%20might%20be%20worth%205%20in%20column%20M%20but%203%20in%20column%20BC.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20an%20example%20of%20my%20scoring%20(using%20random%20columns%20and%20scores%20for%20illustrative%20purposes)%3A%3C%2FP%3E%3CUL%3E%3CLI%3EColumn%20A%20would%20be%20excluded%20completely%3C%2FLI%3E%3CLI%3EBlue%20cells%20(except%20column%20M)%20%3D%203%3C%2FLI%3E%3CLI%3EBlue%20cells%20in%20column%20M%20%3D%206%3C%2FLI%3E%3CLI%3EGreen%20cells%20(except%20column%20M)%20%3D%204%3C%2FLI%3E%3CLI%3EGreen%20cells%20in%20column%20M%20%3D%2010%3C%2FLI%3E%3CLI%3EYellow%20cells%20(except%20column%20M)%20%3D%202%3C%2FLI%3E%3CLI%3EYellow%20cells%20in%20column%20M%20%3D%204%3C%2FLI%3E%3CLI%3EOrange%20cells%20(except%20column%20M)%20%3D%201%3C%2FLI%3E%3CLI%3EOrange%20cells%20in%20column%20M%20%3D%202%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI%20would%20then%20have%20a%20cell%20at%20the%20beginning%20of%20the%20row%20that%20would%20tally%20up%20those%20scores%20and%20would%20be%20color-coded%20itself%2C%20using%20conditional%20formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20point%20me%20in%20the%20right%20direction%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1097747%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1097877%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20Scoring%20Formula%20Based%20on%20Cell%20Color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1097877%22%20slang%3D%22en-US%22%3ESince%20you%20used%20Conditional%20Formatting%20to%20set%20the%20colors%2C%20one%20has%20to%20ask%20(at%20least%20I%20have%20to%20ask)%20what%20are%20the%20values%20in%20those%20cells%2C%20values%20that%20create%20the%20conditions%20that%20generate%20the%20various%20colors%2C%20and%20what%20creates%20THOSE%20values%3F%3CBR%20%2F%3EBecause%20it%20may%20be%20easiest%20to%20do%20the%20scoring%20based%20on%20whatever%20those%20underlying%20formulas%20are%20in%20the%20first%20place.%3CBR%20%2F%3EIt%20would%20help%2C%20in%20other%20words%2C%20if%20you%20gave%20the%20bigger%20picture%3A%20what's%20the%20context%20in%20which%20this%20fancy%20stuff%20is%20taking%20place%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1101531%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20Scoring%20Formula%20Based%20on%20Cell%20Color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1101531%22%20slang%3D%22en-US%22%3EThanks%20Mathetes.%20You%20are%20correct%20that%20the%20CF%20is%20using%20formulas%2C%20however%20I%20have%2070%2B%20columns%20that%20each%20use%201-4%20CF%20formulas%2C%20so%20trying%20to%20write%20a%20formula%20which%20would%20check%20each%20column%20for%20the%20color%2C%20give%20a%20value%20to%20that%20cell%20based%20on%20color%2C%20and%20then%20add%20all%20those%20values%20together%20is%20beyond%20my%20skill%20set.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1101614%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20Scoring%20Formula%20Based%20on%20Cell%20Color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1101614%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F511256%22%20target%3D%22_blank%22%3E%40Schiffy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20may%20have%20missed%20the%20second%20part%20of%20my%20question%2Fobservation%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20had%20written%20%22%3CEM%3Eone%20has%20to%20ask%20(at%20least%20I%20have%20to%20ask)%20what%20are%20the%20values%20in%20those%20cells%2C%20values%20that%20create%20the%20conditions%20that%20generate%20the%20various%20colors%2C%20%3CSTRONG%3Eand%20what%20creates%20THOSE%20values%3C%2FSTRONG%3E%3F%3C%2FEM%3E%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20most%20helpful%20thing%20you%20could%20do%20at%20this%20point%20is%20actually%20upload%20your%20spreadsheet%2C%20or%20a%20sampling%20of%20it.%20Otherwise%2C%20anybody%20out%20here%20is%20kind%20of%20working%20in%20the%20dark.%20Your%20description%20gives%20at%20best%20a%20cloudy%20idea%20of%20what%20you're%20working%20with--words%20alone%20have%20their%20limitation.%20And%20an%20image%20of%20what%20you're%20working%20with--despite%20the%20cliche%20about%20an%20image%20being%20worth%20a%20thousand%20words--an%20image%20alone%20wouldn't%20be%20much%20better.%20We%20need%20to%20see%20the%20underlying%20reality.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1101821%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20Scoring%20Formula%20Based%20on%20Cell%20Color%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1101821%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20apologies%20for%20misreading%20that%20and%20not%20answering%20the%20question.%20Some%20of%20the%20CF%20is%20based%20on%20numerical%20values%2C%20others%20are%20IF%20formulas%20based%20on%20cell%20text%2Fcontent%2C%20and%20some%20are%20a%20combination%20of%20those.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20a%20very%20tiny%20selection%20from%20one%20of%20the%20worksheets%20with%20over%20half%20the%20columns%20removed.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

So I hit a road block on a large spreadsheet (300+ mb) that I've been working on.

 

I've used conditional formatting to color code cells in 15-20 columns. I want to create a formula that will generate a score for that row, based on values that I assign to different colors. Some of the scores for the same color would vary based on the column. For example, a blue cell might be worth 5 in column M but 3 in column BC.

 

Here's an example of my scoring (using random columns and scores for illustrative purposes):

  • Column A would be excluded completely
  • Blue cells (except column M) = 3
  • Blue cells in column M = 6
  • Green cells (except column M) = 4
  • Green cells in column M = 10
  • Yellow cells (except column M) = 2
  • Yellow cells in column M = 4
  • Orange cells (except column M) = 1
  • Orange cells in column M = 2

I would then have a cell at the beginning of the row that would tally up those scores and would be color-coded itself, using conditional formatting.

 

Can anyone help point me in the right direction?

 

Thanks.

4 Replies
Highlighted
Since you used Conditional Formatting to set the colors, one has to ask (at least I have to ask) what are the values in those cells, values that create the conditions that generate the various colors, and what creates THOSE values?
Because it may be easiest to do the scoring based on whatever those underlying formulas are in the first place.
It would help, in other words, if you gave the bigger picture: what's the context in which this fancy stuff is taking place?
Highlighted
Thanks Mathetes. You are correct that the CF is using formulas, however I have 70+ columns that each use 1-4 CF formulas, so trying to write a formula which would check each column for the color, give a value to that cell based on color, and then add all those values together is beyond my skill set.
Highlighted

@Schiffy 

 

I think you may have missed the second part of my question/observation:

 

I had written "one has to ask (at least I have to ask) what are the values in those cells, values that create the conditions that generate the various colors, and what creates THOSE values?"

 

The most helpful thing you could do at this point is actually upload your spreadsheet, or a sampling of it. Otherwise, anybody out here is kind of working in the dark. Your description gives at best a cloudy idea of what you're working with--words alone have their limitation. And an image of what you're working with--despite the cliche about an image being worth a thousand words--an image alone wouldn't be much better. We need to see the underlying reality.

Highlighted

@mathetes 

My apologies for misreading that and not answering the question. Some of the CF is based on numerical values, others are IF formulas based on cell text/content, and some are a combination of those.

 

Attached is a very tiny selection from one of the worksheets with over half the columns removed.