Combining Colours and Existing Values to Create Line Graph

[Anika Sen]

I am looking to devise new metrics in excel using certain combinations.

The first thing is a table that I have created. This table assigns values to 30 items in a list that have been numbered from 1 to 30.

This list is generated based on certain prevailing factors and will be updated on a weekly basis.

Each of the 30 items in the list is color coded based on an existing system of classification. Based on the colors that correspond to each position, I would like to set up a formula or a macro to automatically tally all the values. Based on the values collected pertaining to each color, I would like to create a line graph. Now remember I said I’ll be adding the table with 30 values every week? This graph will capture all the changes recorded over a period of time.

In addition to this, I will also have another metric that similarly takes into consideration colors corresponding to each position and their sum to create a bar graph. This too shall be updated every week.

Any tips on how I can go about creating something like this?

Thanks a ton folks!

 

[Hui]

Anika

Firstly, Welcome to the Chandoo.org Forums

I'd start by recommending that you attach a sample file with one week's data and then a second weeks data so we can see what were dealing with

Then describe the process for assigning values using the example data as a guide

 

[Anika Sen]

Thanks Hui.

I have attached a sample file. As you can see, the colours in the first sheet represent two weeks' data and the values in the second sheet are the ones that I'd like to tally with the corresponding colours.

Let me walk you through the process

1. Using a proprietary process, we will be creating the list of 30 items and each of these items shall be color coded. The five colors we use are – Dark Green, Light Green, Amber, Red and Blue

2. There are two core values that we have determined are called ‘Engagement Factor’ and ‘Individual Score’

3. These both represent values that will be the basis for two separate charts

4. Now, each of the entities in the list have a color assigned to them and depending on where they stand between 1 to 30 also have a score assigned to them

5. Cumulative scores will be calculated depending on color and ‘Engagement Factor’ and, colors and ‘Individual Score’

6. For example, all the things colored red will have a cumulative ‘Engagement Factor’ score as well as an ‘Individual Score’

7. This will be the case for all five colors

8. Based on these cumulative scores, we will have two graphs that showcase the trend over a period of time

 

[AVK]

your idea not clear. I think you define color (1 to 30) as per week1 & List 1-10, List 11-20, List 21-30. If you show / input manually required result its more helpful.