Charting values continuously (not as points) over varying distances

[Be a Pro]

First of all, "Hello all" and thank you for considering my dilemma. It's simply beyond my current skill.

I'm making charts of separate data sets all with data values between 1 and 5 assigned to varying distances. I have to make charts in illustrator for better quality (PhD standards), but it would be a lot simpler to see an excel chart and then copy what I'm seeing rather than reading every single number.

I'm trying to make this:
Chart Example

Out of this:
Spreadsheet Example

The problem is that I need excel to maintain the value between distance ranges, but it is only plotting the beginning point (xy scatter). When there is no value there, however, it needs to be clear that there is nothing there. The "no value" gaps in the data are as important as the values. I would like it to show up in a 1000ft box displaying whatever values are there so that I could just copy and paste 1000ft sections of data into the chart data range and have it display instantly. If I could do that, it would be FAN-TASTIC.

Thoughts?

 

[Cindy Ellis]

Hello, and welcome to Mr. Excel!
Can you please check the linked images? Neither one of them looks anything like a scatter chart. Maybe I'm missing something...

 

[Be a Pro]

Yes, I know. I'm sorry. I was just referencing what I tried that did not work. The scatter chart left gaps in the "bars" of my "Chart Example".

I should also note that the data in the spreadsheet is NOT representative of the chart example. It's a completely different set that I will need to chart.

It might also be helpful to know that any stronger value would be okay to chart. So, if there were different columns for values 1, 2, 3..., it would be alright to say that a value of 3 implies that "value strengths" of 2 and 1 are guaranteed.

Something like:
1
12
1
12
123
1

12
1
123

 

[Cindy Ellis]

I'm still unclear on the expected output. Forgetting Excel for just a minute (heresy, probably) how would you go about manually constructing the chart for just one of the substances?
Looking at the sample chart, it seems that the "x-axis" for a single substance is the strength value and the y-axis is depth (distance?)...but I can't figure out how the "P" "R" and "F" values play into it.

 

[Be a Pro]

I have to admit, I hadn't realized how much the unexplained details might cause confusion. The chart example shows (ignore the large green bar on the left, it is not needed here). The collection of colored columns in the section on the right show the intensity of the presence of different minerals in a rock sample as the footage of the drillhole advances. I hope that clarifies what my intentions are, but if not, please just ask more questions until it is clear.

In the data chart, "F" can be ignored. I usually black it out so I don't accidentally try to chart it. "P" and "R" usually do not occur together, but they are both joined into one data set (of colored three columns).

For example, from the orange column labeled "Gr", I would draw a 1 box wide bar showing "t" (a.k.a 1, or "trace") from 2.5 to 35.2ft rounding up or down (it doesn't need to be that specific) immediately followed by a 2 box wide bar from 35.2ft to 86.9ft showing "m" (a.k.a. 3, or "moderate")

A little further down, I would be drawing another bar from 165.4ft to 200ft ("200" covered by my note "Distance", but shown in the left of the next row down) with a 1 box wide column representing "t" or "trace".

Thank you for your patience.

 

[Be a Pro]

Here is a new spreadsheet example with data that corresponds to the chart I need to make up.

<a href="http://www.screencast.com/t/GitwQF3K">New Spreadsheet Example</a>

Here is the best I've been able to do with a scatter plot. Notice the gaps in the data between distances. I really need it to be continuous like my other cahrt.

<a href="http://www.screencast.com/t/kF0gUcLsMuP">Attempted Scatterplot</a>

I have no idea what made the line stop and start like that.

 

[Cindy Ellis]

I was able to create the chart below by restructuring the data so that the start and end of each depth range is included. It makes the data a bit less intuitive for human reading, so it's probably best to create a copy of your data and work from that. Rearranging the data manually would likely become a problem if you have to do this for several locations, but I figured you could give thumbs-up or down on the chart before figuring out how to automate it.
Here's how the data was rearranged:

Excel 2007
	C	F	G	H
1	Distance	Bio
2	Range	P	R	F
3	0	2
4	0.8	2
5	0.8	0
6	1.4	0
7	1.4	1
8	14	1
9	14	0
10	15	0
11	15	1
12	16.1	1
13	16.1	0
14	25.4	0
15	25.4	1
16	27.5	1
17	27.5	1
18	34.6	1
19	34.6	2
20	35.7	2
21	35.7	1
22	36.1	1
23	36.1	2
24	38.4	2
25	38.4	1
26	40.7	1
27	40.7	2
28	43.7	2
29	43.7	2
30	48.3	2
31	48.3	1
32	57	1
33	57	2
34	60.7	2
35	60.7	1
36	65.1	1
37	65.1	2
38	68.8	2
39	68.8	0
40	69.5	0
41	69.5	0
42	72.7	0
43	72.7	0
44	75	0
45	75	1
46	86.6	1
47	86.6	1
48	90.7	1
49	90.7	2
50	92.7	2
51	92.7	2
52	112.8	2
53	112.8	3
54	114	3
55	114	0
56	117	0
Sheet1

Columns D and E are just hidden for purposes of displaying here. The big change was duplicating every row, but then changing every other value to represent the start of the range, so the signal value is shown for the start of the depth range and the end of the depth range; the chart then draws a line between the 2.


Here's the chart output:

Is that close to what you are trying to accomplish?

 

[Be a Pro]

That's exactly it! Genius.

Just to clarify, that is still a scatter chart, right? Additionally, is it possible to fill color on the value side of the line?

And THANKS!

 

[Cindy Ellis]

Yes it's a scatter plot, but it took some finagling to force Excel to do what I was telling it to.
To the best of my knowledge, there's no way to fill in the space to the left of the line...but it's intriguing to me so I'll do some looking around to see what I can find (and I tend to like helping with PhD stuff...I always learn something new )
In the formula bar, the line definition is

=SERIES(Sheet1!$F$1,Sheet1!$F$3:$F$56,Sheet1!$C$3:$C$56,1)

...but when you do this initially it will default to data in rows instead of columns, or try to plot both the x and y values as y-series against a sequence-based x axis. I had to do a bit of playing around with the chart editor to get the right outcome.
I'll see if I can come up with a macro to automate the really tedious parts. In the mean time...it IS possible, just a bit of a nuisance.
If it were my project, given that the purpose is to just get it into a form that Illustrator can trace reliably, I would probably get the chart set up to reference a specific range of data, then copy/paste each data set into that data range, then copy/paste the chart as an image.

 

[Be a Pro]

The chart looks great, Cindy, but now that I've tried to duplicate it, I comprehend how the data was changed. Unfortunately, I really need a way to either paste raw data "as is" or run something that quickly changes it into what you had there. I am now stumped. I've got 40,000 feet with about 8-10 data sets for each foot to finish so I can't be reconfiguring data in any dramatic way, but +1 for your efforts.