is there an excel function that turns the text color of a cell into a value?-CodePudding

I need to perform some averageif and sumif functions on a database whose criteria is defined by the colour of the text in each row.
Currently, I am manually assigning to each row a value based on the colour of the text (1 for red and 2 for black) in order to perform calculations. The problem is that my file if huge and it's not possible to keep assigning values manually.
I was wondering if there was some excel function that returns a value based on the colour of the text (or any other solution that saves time).

Thank you

CodePudding user response：

In case VBA is okay in your use case, you can write a function and place it in Module1. You may need to create the Module1 (Alt F11, Locate your Project, right click and select Insert --> Module)

Function getCol(Rng As Range) As Variant
    Dim ColorValue As Variant
    ColorValue = Cells(Rng.Row, Rng.Column).Interior.Color
    getCol = Rng.Interior.ColorIndex
End Function

You can then use the formula like below

=getCol(E5)

CodePudding user response：

I believe the story is not entirely as you say it is, because to my knowledge, you can't store a colour in a database :-)

So, in my opinion, you have two columns in database, one with some label, the other with some value (1 or 2) and you have decided to merge both informations into one column in Excel, where you want the value 1 to be represented by a red colour and 2 by a black colour. (Most probably you either want to emphasize the presence of those values)

For that reason, you can use conditional formatting, as I'm showing you in the following screenshot (using green instead of black):

You can, obviously, choose to hide the column with the value, that won't make a difference for the conditional formatting:

Obviously, your Sumif and AverageIf functions will be based on the values of the B column.