Help with charts

[CivilPengineer]

I want to use the charts to do the following:
I have data where each data point has an "X" value, "Y" value (co-ordinates) and a "UR" value. These are in three columns, each data point is on a seperate row. Here is some dummy data (real data is a lot bigger):

What I want to do is make a chart where it shows the location of each data point in X,Y, and then colours the point green if the "UR" value for that point is less than or equal to 1, and red if it is greater than 1.

It will look something like this (although I had imagined using a chart to do this, not cells in the workbook)

Can anyone help? I was thinking I might be able to do something with the 3d surface chart but I can't get it to work.

Any ideas much appreciated!

 

[Steve=True]

Hi Civil P Engineer.

Welcome to the forums!

Here is my draft of what you are trying to do.

It is not in 3d, is that okay?

 

[CivilPengineer]

That looks fine, how did you do that? Assuming that can be extended to automatically read hundreds of lines of data and make a much bigger chart, that will be perfect

Thanks!

 

[Steve=True]

Great. Yes it will scale up. I am no engineer, so what is the context?

 

[CivilPengineer]

It's a plot of various node points on a wall within a building. For each point I have other calculations determining whether the building structure is strong enough at that point, which is represented by the UR value. If UR <1, the structure is OK at that point, if UR >1, the structure is not OK at that point.

Can you let me know how you made the chart? I'm off home now but I'm excited to try it out on some of my real data

Thanks for your help
Luke

 

[Steve=True]

That is really interesting.

Here are the steps:

http://youtu.be/ZC2TQFY3T8Q

Please let me know if it helps!

Thanks

Steve=True

 

[CivilPengineer]

This is fantastic! I have followed your excellent youtube video, first to replicate what you did with the sample data, then extended it to my real data. The results can be see below:

Turns out the real data points aren't quite so regularly arranged as my imaginary sample data, so I swapped to diamonds to reduce overlapping. But this is great, it shows a good visual representation of the wall and highlights the area of concern at the bottom right really clearly. Much better than searching through 900+ lines of data for the URs >1. I'm very happy with this!

Thanks a lot for your help, and for making such a clear and instructive video. I will share it around my office as I'm sure a lot of my colleagues will find this useful.

Again, many thanks!

Luke

 

[Steve=True]

That is so awesome. Thanks for sharing your final chart:

I have posted step-by-step instructions here as well.

http://www.exceldashboardtemplates.com/?p=1602

So glad it helped!!

Steve=True

 

[Steve=True]

I luke, i was thinking about your new post where you stated that there are overlapping points.

One fix for this is to move your RED data UP to the top in the Select Data Dialog Box. That way it will show on top of the chart and then RED will always be on top.

Thanks again for your very kind comments!

 

[CivilPengineer]

Hi Steve
I even took it a bit further and made various colours for different ranges of UR. Here blue describes the range 0 < x < 0.25, green up to 0.5, yellow up to 0.75, orange up to 1, and red for x > 1.<ur<=0.25, green="" describes="" 0.25<ur<="0.5," yellow="" is="" 0.5<ur<="0.75" and="" orange="" 0.75<ur<="1." red="" still="" ur=""> In case you're wondering why the red points aren't the same as before, this is a different set of data to the one I posted before (other face of the wall).

This gives a better idea of the stressed areas in the wall (more stress = higher UR) and gives something meaningful to examine for other walls where no UR is greater than 1.

Thanks for the tip about the order, I will arrange it so that red is on top, then orange, yellow etc.

Thanks again for the help

Luke</ur<=0.25,>