Question description:
This user has given permission to use the problem statement for this
blog.
Hello, I am trying to create a total color column? Can anyone help?
Solved by I. A. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
26/04/2018 - 10:24
Hi
Excelchat Expert
26/04/2018 - 10:24
Hello! Welcome to Excel chat.
User
26/04/2018 - 10:24
I believe I'm using 2010
User
26/04/2018 - 10:24
Thanks! I hope you can help!!!
Excelchat Expert
26/04/2018 - 10:24
I understand that you want to make a total column. Please share more details about that.
User
26/04/2018 - 10:25
sure
Excelchat Expert
26/04/2018 - 10:25
It would be good, if we can have some sample data as well. Only 2-3 rows of data should be sufficient.
User
26/04/2018 - 10:25
I'm creating an evaluation worksheet that's rated on color. Green, Yellow, Red
Excelchat Expert
26/04/2018 - 10:25
Ok.
User
26/04/2018 - 10:26
I have 3 yes or no questions - yes would = green and no would = red. I also have 3 green, yellow, red questions.
Excelchat Expert
26/04/2018 - 10:26
Ok.
User
26/04/2018 - 10:27
I need color total to capture the accurate rating. I hope that clears up any confusion.. Need more info?
Excelchat Expert
26/04/2018 - 10:27
Ok.
Excelchat Expert
26/04/2018 - 10:28
Yes, sure I need to understand better.
Excelchat Expert
26/04/2018 - 10:28
Let me ask my questions based on the understanding so far.
User
26/04/2018 - 10:28
kool
Excelchat Expert
26/04/2018 - 10:28
Do you want the total for each color?
User
26/04/2018 - 10:29
in that regard. Yes/Green can = 3, yellow would = 2 and No/red would = 1
Excelchat Expert
26/04/2018 - 10:29
Ok.
Excelchat Expert
26/04/2018 - 10:30
Ok. I have added some dummy data in the online sheet.
User
26/04/2018 - 10:30
for example if column c reflected all greens I need the total to be the color green
Excelchat Expert
26/04/2018 - 10:30
Ok.
User
26/04/2018 - 10:30
I can't see it
Excelchat Expert
26/04/2018 - 10:30
Please refresh your browser.
User
26/04/2018 - 10:31
will is loose you if do?
Excelchat Expert
26/04/2018 - 10:31
No. You won't.
User
26/04/2018 - 10:32
I like it.. Now we need a total column that would choose the best or highest color (make sense?)
Excelchat Expert
26/04/2018 - 10:32
Ok.
Excelchat Expert
26/04/2018 - 10:32
Let's first capture total of each color, then we can devise a way to choose the best or highest color.
User
26/04/2018 - 10:33
ooh.. sounds promising
User
26/04/2018 - 10:33
how do we get started?
Excelchat Expert
26/04/2018 - 10:33
Just hold on. I am adding formula.
User
26/04/2018 - 10:34
okay, my apologies
Excelchat Expert
26/04/2018 - 10:35
No worries. Now there are totals in column D, based on the weight given to each color (Green =3, Yellow = 2, Red = 1)
Excelchat Expert
26/04/2018 - 10:35
Now basis that, we can see that max is for green.
User
26/04/2018 - 10:35
I'm lost, can you explain it differently.. sorry:(
Excelchat Expert
26/04/2018 - 10:35
Ok
Excelchat Expert
26/04/2018 - 10:36
Please see the totals in column D.
User
26/04/2018 - 10:36
I may have it.. the total for D2 should be 2 not 3 as red = 1
Excelchat Expert
26/04/2018 - 10:36
But there are three Red in data.
User
26/04/2018 - 10:37
okay, hold on
User
26/04/2018 - 10:37
I see the two green and the one additional green in B7.
User
26/04/2018 - 10:37
that totals 9 - yes?
Excelchat Expert
26/04/2018 - 10:37
Don't look at column C yet.
User
26/04/2018 - 10:37
ah, got it now
Excelchat Expert
26/04/2018 - 10:37
That's just labels for our color totals.
User
26/04/2018 - 10:38
whew!!!! clarity is wonderful
Excelchat Expert
26/04/2018 - 10:38
My apologies for mixing them up.
Excelchat Expert
26/04/2018 - 10:38
Now we can focus on column E as the result column.
Excelchat Expert
26/04/2018 - 10:38
B is the column where our data is.
User
26/04/2018 - 10:38
no worries..
Excelchat Expert
26/04/2018 - 10:39
In column B, there are total 7 entries, 2 Green, 3 Red, 2 Yellow.
Excelchat Expert
26/04/2018 - 10:39
Right?
User
26/04/2018 - 10:39
yep
Excelchat Expert
26/04/2018 - 10:40
Ok great! I have simply counted the number of each color in column B and multiplied it by their weight.
User
26/04/2018 - 10:40
or add
Excelchat Expert
26/04/2018 - 10:40
And that gives me the total for that color respectively.
User
26/04/2018 - 10:40
yep got it!
Excelchat Expert
26/04/2018 - 10:41
Now, that we have the total value for each color, we can use it to determine the best color.
Excelchat Expert
26/04/2018 - 10:41
If we take simple criteria of the highest value, then the best color would be 'Green'.
Excelchat Expert
26/04/2018 - 10:41
I hope that answers your question.
User
26/04/2018 - 10:41
sessions going to end soon
User
26/04/2018 - 10:42
i know that but how to I reflect that in a formula?
Excelchat Expert
26/04/2018 - 10:42
No worries. You can still extend it for one more session. You already on the 2nd one now.
User
26/04/2018 - 10:42
okay
User
26/04/2018 - 10:42
thx
Excelchat Expert
26/04/2018 - 10:42
Ok. If you need a single line formula, then we can use a Max function.
Excelchat Expert
26/04/2018 - 10:42
Let me show you that.
User
26/04/2018 - 10:43
hmmm. explain
Excelchat Expert
26/04/2018 - 10:44
We will use a nested if to get the color name.
User
26/04/2018 - 10:44
thx
User
26/04/2018 - 10:44
nested?
User
26/04/2018 - 10:44
I'm unfamiliar with that term
Excelchat Expert
26/04/2018 - 10:44
An IF function within other IF function.
User
26/04/2018 - 10:45
sounds complex but I guess it would have to be
User
26/04/2018 - 10:46
you still with me
Excelchat Expert
26/04/2018 - 10:47
Yeah I am working on the formula.
User
26/04/2018 - 10:47
kool
Excelchat Expert
26/04/2018 - 10:48
There is a partial formula in E5 right now.
Excelchat Expert
26/04/2018 - 10:48
I am modifying it further.
User
26/04/2018 - 10:49
looks good. For the example you provided. As red and yellow equal red would trump the yellow.
User
26/04/2018 - 10:49
Wow!!! FABU
Excelchat Expert
26/04/2018 - 10:50
I hope that solves your problem.
Excelchat Expert
26/04/2018 - 10:50
:)
User
26/04/2018 - 10:50
me too.. I think it will.. Can you share the formula?
Excelchat Expert
26/04/2018 - 10:50
=if(countIF(B1:B7,"Green")*3 >countIF(B1:B7,"Yellow")*2, If(countIF(B1:B7,"Green")*3 > countIF(B1:B7,"Red")*1, "Green", "Red"),if(countIF(B1:B7,"Yellow")*2 >countIF(B1:B7,"Red")*1,"Yellow", "Red") )
Excelchat Expert
26/04/2018 - 10:51
That's the formula. Or you can download the online sheet to your local PC from 'File' menu.
User
26/04/2018 - 10:51
how?
Excelchat Expert
26/04/2018 - 10:51
Go to File menu on the sheet.
Excelchat Expert
26/04/2018 - 10:51
And then in the menu, select 'Download as'
Excelchat Expert
26/04/2018 - 10:52
Select the format as Microsoft Excel and save it to the folder you wish to on your local drive.
User
26/04/2018 - 10:52
done
Excelchat Expert
26/04/2018 - 10:52
Cool
Excelchat Expert
26/04/2018 - 10:52
Should I explain the formula now?
User
26/04/2018 - 10:52
Oh my gosh.. Thank you so much!!!!
Excelchat Expert
26/04/2018 - 10:53
Should I explain this formula to you?
User
26/04/2018 - 10:54
I think so
Excelchat Expert
26/04/2018 - 10:54
Ok. Fine.
User
26/04/2018 - 10:54
because I'm still struggling to apply it
Excelchat Expert
26/04/2018 - 10:54
Ok. I will explain it so that you can modify to suit your needs.
User
26/04/2018 - 10:54
thank you!
Excelchat Expert
26/04/2018 - 10:55
There are two parts of the formula, first is to calculate the total for each color.
Excelchat Expert
26/04/2018 - 10:55
You can ignore D1 to E3 for now, and lets focus just on E5.
Excelchat Expert
26/04/2018 - 10:56
So, the first part is calculating total for a color and multiplying it by its weight.
Excelchat Expert
26/04/2018 - 10:56
This is being done by using a COUNTIF function.
Excelchat Expert
26/04/2018 - 10:56
COUNTIF is the function to count values using a criteria.
Excelchat Expert
26/04/2018 - 10:57
As you would see, I have used B1:B7 is the range where I need counting, and second input is the color name within double quotes.
Excelchat Expert
26/04/2018 - 10:57
To apply this formula to your case, you just need to change the range in each COUNTIF function.
Excelchat Expert
26/04/2018 - 10:58
So if your data is C2 to C100, you will need to put C2:C100 in each COUNTIF function in the formula.
Excelchat Expert
26/04/2018 - 10:59
Now, the remaining explanation: I have used multiple IF in a main IF.
Excelchat Expert
26/04/2018 - 10:59
In main IF, I am checking if total of Green is larger than total of Yellow.
User
26/04/2018 - 11:00
Question.. I've adjusted the formula but the yielding color is yellow instead of red
User
26/04/2018 - 11:00
IF(COUNTIF(E11:E17,"Green")*3 >COUNTIF(E11:E17,"Yellow")*2, IF(COUNTIF(E11:E17,"Green")*3 > COUNTIF(E11:E17,"Red")*1, "Green", "Red"),IF(COUNTIF(E11:E17,"Yellow")*2 >COUNTIF(E11:E17,"Red")*1,"Yellow", "Red") )
User
26/04/2018 - 11:00
this is the adjusted formular
Excelchat Expert
26/04/2018 - 11:01
Are Red and Yellow equal?
User
26/04/2018 - 11:01
they are no
User
26/04/2018 - 11:01
sorry they are not equal
Excelchat Expert
26/04/2018 - 11:02
Then formula is right.
User
26/04/2018 - 11:02
there's more red than yellow so the rating should be red
Excelchat Expert
26/04/2018 - 11:02
But the weight of Yellow is more, so that's why Yellow may come up.
User
26/04/2018 - 11:02
I also have to include a rating of yes or no answers
Excelchat Expert
26/04/2018 - 11:03
If number of Reds are more than double of number of Yellows, only then answer can be Red
User
26/04/2018 - 11:03
eliminating yes or no answers would yield a yellow rating
Excelchat Expert
26/04/2018 - 11:03
Else it will always be Yellow.
User
26/04/2018 - 11:03
is it possible to include a yes or no rating with the formula that you created?
Excelchat Expert
26/04/2018 - 11:04
It is, but that will make formula way too complex. And I would advise against that.
User
26/04/2018 - 11:04
any suggestions?
User
26/04/2018 - 11:05
I could create a second column to rate the yes or no answers
Excelchat Expert
26/04/2018 - 11:05
Then you can write a result table, as we did initially in D1 to E3.
User
26/04/2018 - 11:05
then divide the two columns? does that make sense
Excelchat Expert
26/04/2018 - 11:06
I didn't get that.
User
26/04/2018 - 11:06
yeah.. I was just trying to make sense :)
User
26/04/2018 - 11:06
I can have two total columns
User
26/04/2018 - 11:06
one for the green, yellow, red
User
26/04/2018 - 11:07
and the other for the yes or no
Excelchat Expert
26/04/2018 - 11:07
Ok. Yeah, that can be done.
User
26/04/2018 - 11:07
Kool.. can you show me?
Excelchat Expert
26/04/2018 - 11:08
Please see the cell E6 now.
User
26/04/2018 - 11:09
I see it
Excelchat Expert
26/04/2018 - 11:09
I have two columns now, and we can know whether "Yes" or "No" has the most numbers.
User
26/04/2018 - 11:10
not to add more oil to the fire but yes = green and no = red
User
26/04/2018 - 11:11
is it possible to intertwine the two to get an accurate rating?
Excelchat Expert
26/04/2018 - 11:11
Aah Ok.
User
26/04/2018 - 11:11
between the best color and the most yes or no
User
26/04/2018 - 11:11
awesome!!!
Excelchat Expert
26/04/2018 - 11:11
Just hold on.
User
26/04/2018 - 11:11
:)
Excelchat Expert
26/04/2018 - 11:13
It's difficult now.
User
26/04/2018 - 11:13
oh man..
User
26/04/2018 - 11:13
shoot
Excelchat Expert
26/04/2018 - 11:13
As I don't know the whole context, I am not able to find the relation between those two.
User
26/04/2018 - 11:14
okay.. I'll try to explain
Excelchat Expert
26/04/2018 - 11:14
I understand that you gave colors based on the Yes / No answers only.
Excelchat Expert
26/04/2018 - 11:14
So adding a second column of Yes / No will not add any value to it.
User
26/04/2018 - 11:15
okay so change the yes to equal 3 and the no to equal 1. 3 in turn = green and 1 would in turn = red
Excelchat Expert
26/04/2018 - 11:15
In my opinion, it will just doubles up Yes / No weight. And more so for Green.
Excelchat Expert
26/04/2018 - 11:15
I have already done that in E6. I have given Yes = 3, Red = 1.
Excelchat Expert
26/04/2018 - 11:16
But I don't know how to combine best color and most yes to get a final color. As I don't see any value in it.
User
26/04/2018 - 11:16
is there a way to create a value?
Excelchat Expert
26/04/2018 - 11:16
And in my opinion, will not reflect a right rating.
Excelchat Expert
26/04/2018 - 11:17
My suggestion is to use only either color or Yes / no to get a rating.
Excelchat Expert
26/04/2018 - 11:17
Use only 1 of them.
User
26/04/2018 - 11:17
okay..
User
26/04/2018 - 11:17
let's do it..
Excelchat Expert
26/04/2018 - 11:18
Then the best color formula is the right one.
Excelchat Expert
26/04/2018 - 11:18
You can ignore column C now.
User
26/04/2018 - 11:18
sounds good!!!
User
26/04/2018 - 11:18
for those three yes/no questions, 3 and 1 would be the numbers to choose from to equal green or red
Excelchat Expert
26/04/2018 - 11:18
Yes.
User
26/04/2018 - 11:18
sounds like a plan..
Excelchat Expert
26/04/2018 - 11:18
And that's the formula in E7.
User
26/04/2018 - 11:19
for column C?
Excelchat Expert
26/04/2018 - 11:19
yes.
User
26/04/2018 - 11:19
okay,.. will you show me the formula?
Excelchat Expert
26/04/2018 - 11:19
=If(countif(C1:C7, "Yes")*3 > CountIf(C1:C7, "No")*1, "Green", "Red")
Excelchat Expert
26/04/2018 - 11:20
You should save the file now locally.
User
26/04/2018 - 11:21
Wow!! That was amazing... Thanks so much for your help!!!
User
26/04/2018 - 11:21
can I know your name?
Excelchat Expert
26/04/2018 - 11:21
I am glad that I could help you.
Excelchat Expert
26/04/2018 - 11:22
Although we are not allowed to share our personal info, my first name is Pulkit.
User
26/04/2018 - 11:22
Well Pulkit.. You saved the day!!! :)
Excelchat Expert
26/04/2018 - 11:22
Thanks for kind words.
User
26/04/2018 - 11:22
I'm ever so grateful!!!
Excelchat Expert
26/04/2018 - 11:22
Hope to see you here again soon. Have a nice day ahead.
User
26/04/2018 - 11:23
You too!!!
Excelchat Expert
26/04/2018 - 11:23
Bye!
Excelchat Expert
26/04/2018 - 11:24
You can end this session now by clicking END button above. And please don't forget to rate your experience.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
