Combining Colours and Existing Values to Create Line Graph

[anikasen]

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!

 

[Gerald Higgins]

Hi, welcome to the board.

What you want to do is probably do-able, but to me, your request is far, far too vague to allow me to give a detailed answer.

Please can you explain in more detail exactly what you want to do ?

Don't worry about explaining the real world application of your data, that's probably not important right now.
Just focus on describing exactly what your data looks like, and what exactly you want to do with it.

 

[anikasen]

Thanks for your reply Gerald.

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.’ Certain values exist in these two categories. A specific unique value exists for all entities from 1 to 30
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

 

[Gerald Higgins]

Can you show us some of your data ?

If you don't want to post real data, make up some data that looks like the real data in all important logical respects.

 

[anikasen]

I cant see a way to add an attachment here. Can you please help me how to do this?

 

[Gerald Higgins]

See the forum guidelines for advice on how to post data.
http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

Or, try typing it directly into the text of your post, if you're only posting a small amount.

 

[anikasen]

I have attached some references. The first is a screenshot of the 30 results spread over two weeks. These include the color codes discussed above

The second is a representation of the values I want to assign to each position in the list of 30 results.

[TABLE="width: 435"]
<tbody>[TR]
[TD]List Number[/TD]
[TD]Engagement Factor[/TD]
[TD]Individual Score[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]33[/TD]
[TD]990[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]17[/TD]
[TD]480[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]12[/TD]
[TD]320[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8[/TD]
[TD]210[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6[/TD]
[TD]160[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4[/TD]
[TD]93[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3.8[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3.7[/TD]
[TD]83[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]3.6[/TD]
[TD]78[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4.2[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]4.1[/TD]
[TD]79[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]4[/TD]
[TD]74[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]4[/TD]
[TD]69[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]4[/TD]
[TD]64[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]3.9[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]3.8[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]3.7[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]3.7[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]3.8[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]1.8[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]1.7[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]1.6[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]1.5[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]1.4[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]1.3[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]1.3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]1.3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]1.3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]1.3[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

(To quote Midge Ure) "This means nothing to me"