• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Color Code for Scatter Plot

I want to provide color code for scatter plot on the basis of matching the certain condition.
For e.g. if Sector in column C=Manufacturing, then the color should be Yellow, if its A&D then the color should be Blue, if its Chemicals, then the color code should be Green.

Please help me with the same.
 

Attachments

  • Macro for Scatter Chart.xlsm
    43.6 KB · Views: 5
I want to provide color code for scatter plot on the basis of matching the certain condition.
For e.g. if Sector in column C=Manufacturing, then the color should be Yellow, if its A&D then the color should be Blue, if its Chemicals, then the color code should be Green.

Please help me with the same.
It would also be very helpful if anyone could also advise how to shade the quadrant by dividing into four on the basis of average value of x and y axis
 
msharma864512
... or something like this?
This is gud,

but my dataset is a bit dynamic and the score which you divided into three columns on the basis of their sector, may not work with my actual data, because, my overall score changes after applying certain condition, and it get pulled up from some other worksheet,

And in many cases, numnber of companies in the list can be more than the list in this spreadsheet and in some case it can be lesser too.

So, the charts have to be created by defining the range
 
msharma864512
If You wrote (#1):
I want to provide color code for scatter plot on the basis of matching the certain condition.
For e.g. if Sector in column C=Manufacturing, then the color should be Yellow, if its A&D then the color should be Blue, if its Chemicals, then the color code should be Green.
Please help me with the same.
And now, You write that Your needs are 'a bit different'.
If You write this (#1) and You think/need that (#4)...
You'll get this (#1) -
not that (#4), which You haven't written.
 
msharma864512
If You wrote (#1):
I want to provide color code for scatter plot on the basis of matching the certain condition.
For e.g. if Sector in column C=Manufacturing, then the color should be Yellow, if its A&D then the color should be Blue, if its Chemicals, then the color code should be Green.
Please help me with the same.

And now, You write that Your needs are 'a bit different'.
If You write this (#1) and You think/need that (#4)...
You'll get this (#1) -
not that (#4), which You haven't written.
Ya I got ur point, my bad,

But I hope u r getting my requirement here, I want the chart to be dynamic, if there is only one constant data, then ur solution is perfect.

But as my requirment has change, it may not be of much of the help to me.

Hence, it would be very kind of u, if u could help me with my requirement

and keep it under assumption that Scores, in column C will keep on changing, so spliting the data set into three will not help, and even if i break into three, then i would have to use define the range to create the chart, but it should exclude blank cell too.
 
msharma864512
What are Your 'named requirement'? ... has change, not so clear!
Would there be always at least one Sector ... and what would be max number?
If You use term 'dynamic' ... You would open Your term.
If everything should be 'dynamic' then it would be 'amoeba'!
Why there are those columns F&G? ... just a repetition!
If something should pull somewhere ...
You should write what!
So far, Excel cannot think!
... and if someone else should guess ... that would be 50/60 correct or not!
 
Hi,
I have attached a new spreadsheet, here u will see how the data changes.

I have mentioned the details in spreadsheet only
 

Attachments

  • Macro for Scatter.xlsm
    926.4 KB · Views: 3
msharma864512
Where is something 'dynamic'?
Test this more like static ...
I used my previous reply.
Please, next times, Your sample file would be more closer to Your needs.
 

Attachments

  • Macro for Scatter.xlsm
    1,000 KB · Views: 3
msharma864512
Where is something 'dynamic'?
Test this more like static ...
I used my previous reply.
Please, next times, Your sample file would be more closer to Your needs.
Sir the dynamic here are the filters, when u try to use the drop down from cell D3:H3, u will see the chart is not working.

Now try doing the same thing with the charts i created in my version i attached earlier.

My charts should change according to the filters and if there any blank cells it should not consider them into the charts.
 
msharma864512
Sir the dynamic here are the filters, when u try to use the drop down from cell D3:H3, u will see the chart is not working.
You should 'tell' to Excel to do something to chart ... as You wish it to do.
... and there are many 'non-blank cells' ...
> Did You check my file?
If You change those D3:H3 ... what will happen?
 
msharma864512
Sir the dynamic here are the filters, when u try to use the drop down from cell D3:H3, u will see the chart is not working.
You should 'tell' to Excel to do something to chart ... as You wish it to do.
... and there are many 'non-blank cells' ...
> Did You check my file?
If You change those D3:H3 ... what will happen?
> Yes, I did, but its giving wrong data
 
msharma864512
It gives Your data!
What is wrong? Can You give few values?
Not some looooong sentences which won't help!
I have attached two screenshot,
File named Ur chart is the screenshot of the scatter plot created after selecting 2 from dropdown in cell "E3"
File named my chart is the screenshot of the scatter plot created after selecting 2 from dropdown in cell "E3"

Now u can compare the two to see the difference of presentation.

Only difference between urs and my chart is, that my chart doesnt include color coding.
 

Attachments

  • Ur Chart.png
    Ur Chart.png
    10.5 KB · Views: 1
  • my chart.png
    my chart.png
    23.5 KB · Views: 3
msharma864512
I've tried to help You.
I asked: Can You give few values?
Do You know what is a value? ... numbers, maybe?
Values gives/creates that chart ... okay?
> I modified SAME FILE ...
Now, You can see those values which creates that chart in D/E/F-columns.
Are those values correct? ... those are same as in B-column!
> What would be 'Your values'?
> And if You compare values to chart ... hmm?

>> Did You take care that eg 'Score Type' is same in both files?
Screen Shot 2018-06-06 at 11.14.42.png
... and just notice that Your 'dynamic' ranges for chart are not correct!
Those should have same amount of rows!
Screen Shot 2018-06-06 at 11.18.13.png Screen Shot 2018-06-06 at 11.18.53.png
... is Your chart Okay? Can it show correct?
 

Attachments

  • Macro for Scatter.xlsm
    987.4 KB · Views: 3
See attached.
Your first change will colour the points.
 

Attachments

  • Chandoo38781Macro for Scatter.xlsm
    928.5 KB · Views: 16
msharma864512
I've tried to help You.
I asked: Can You give few values?
Do You know what is a value? ... numbers, maybe?
Values gives/creates that chart ... okay?
> I modified SAME FILE ...
Now, You can see those values which creates that chart in D/E/F-columns.
Are those values correct? ... those are same as in B-column!
> What would be 'Your values'?
> And if You compare values to chart ... hmm?

>> Did You take care that eg 'Score Type' is same in both files?
View attachment 52746
... and just notice that Your 'dynamic' ranges for chart are not correct!
Those should have same amount of rows!
View attachment 52748 View attachment 52749
... is Your chart Okay? Can it show correct?

I beleive there must have seem communication gap from my side to state my query. But thank you very much for ur tremendous help. My query is solved now.
 
See attached.
Your first change will colour the points.
Hi Pascal,

I have a small query related to the code you have provided me to change the color of the scatter plots.

Earlier where there were three colors, now I have added two more dataset.

So I would be needing five colors now. I have tried to change the code, but its now working.

Please help me with the same.

Regards,
Manish
 

Attachments

  • Chandoo38781Macro for Scatter.xlsm
    618.3 KB · Views: 2
For you to test: Try changing:
Code:
    Sectors = Array("Manufacturing", "Chemicals", "A&D")
  Colours = Array(RGB(255, 230, 0), RGB(44, 151, 62), RGB(0, 163, 187))
to:
Code:
  Sectors = Array("Manufacturing", "Chemicals", "A&D", "Auto", "Tech")
  Colours = Array(RGB(255, 230, 0), RGB(44, 151, 62), RGB(0, 163, 187), RGB(153, 153, 153), RGB(112, 48, 160))
 
Back
Top