Stepwise plots 3

[Mccoy]

Dear all,
My issue is interval or stepwise plots similar to those in the portrayed illustration (soil parameters versus depth). I could not find a direct way to plot them so I had to use an IF statement. The IF statement I used has also a problem that is, if the value is not the same for at least two consecutive cells, the stepwise pattern is disrupted. I'm also attaching the Excel file I used (Excel 2013). I need a stepwise plot since the soil properties are often measured as a spatial average over a depth interval and not a single point along the investigated vertical. Is there an easy way around this? My datasets contain usually no more than 60 interval values. Depth along the X-axis would be all right as well.

www.mccoy.it

 

[SkipVought]

hi,

Your file apparently has some problems, as my laptop indicates...

Duplicate headers received from server

Hide details
The response from the server contained duplicate headers. This problem is generally the result of a misconfigured website or proxy. Only the website or proxy administrator can fix this issue.
Error code: ERR_RESPONSE_HEADERS_MULTIPLE_CONTENT_DISPOSITION

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[IDS]

Download worked for me. I right-clicked, selected save-as and removed the first dot from the file name.

I will have a look and respond later.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IRstuff]

The malformed filename causes problems in Chrome, but Firefox can handle it.

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com:

http://www.engineering.com/AskForum/aff/32.aspx

 

[Denial]

As can Internet Explorer.

 

[Mccoy]

I forgot to say, the depth intervals in this example and in the applications I'm interested into are all of the same lenght.

http://www.mccoy.it

 

[IRstuff]

What does the original data look like? This looks like something you processed already.

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529


Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com:

http://www.engineering.com/AskForum/aff/32.aspx

 

[Mccoy]

The original data are vectors of numbers (arranged in columns), each number is a spatial average of 1 meter in the above example, the original data are in the attached sheet if it was possibl for you to download it.

I'd like to process the data of one of my latest geotehnica investigations. I'm going to attach it here. First column, depth. Other 4 columns, N20, Number of penetrometer blows per 20 centimeters of depth. I can visualize immediately a plot like this one:

But I'm not able to visualize immediately a stepwise plot of the same data. Data vectors should look like the edges of overlapping bar graphs. That is, each data should have a lenght of 20 cm interval depth.

www.mccoy.it

 

[SkipVought]

Your PLOT data ought to look something like this (just using ONE series) using SCATTER with Straight Lines...

[pre]
Profondità DPSH1
0.2 0
0.2 9
0.4 9
0.4 3
0.6 3
0.6 1
0.8 1
0.8 1
1 1
...
[/pre]

I applied Named Ranges to the source data using Create Names in TOP row.

The Formula in B3 and following. (B2 contains 0 hard coded)
[tt]
B3: =IF($A3=$A2,INDEX(INDIRECT(B$1),MATCH($A3,INDIRECT($A$1),0),1),B2)
[/tt]


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Oh, yes for the X axis values

A2: 0.2
A3: 0.2
A4: = A2+.2

copy A4 down as far as needed.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

My Output

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Mccoy]

Skipvought, thanks very much, the plot is just what I wanted, I never applied the Excel functions you used. I just eliminated the zeros so that it is clear where the single tests (resistance vectors) end.
I'm going to attach the graph so that the desired effect is clear to everyone.

http://www.mccoy.it

 

[IDS]

Alternative set up, which is slightly simpler, and will deal with data with non-uniform depth increment:

With the data as in the sample file:
In Row 5, Columns I to M enter 1,2,3,4,5
In Row 6, Columns I to M enter 0, =B6, =C6, =D6. =E6 (i.e. 0, then values from first row of data)
In Column H from row 6 downwards enter 1, 1, then =H6+1 and copy down as far as required.
In I7: =INDEX($A$6:$E$81,$H6,I$5)
In J7: =INDEX($A$6:$E$81,$H7,J$5) and copy across to Column M
Copy I7:M7 down as far as required.

See attached file.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Mccoy]

Thanks IDS, I must absolutely start studying that INDEX function.

http://www.mccoy.it