×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Stepwise plots3

## Stepwise plots

(OP)
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.

### RE: Stepwise plots

hi,

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

#### Quote:

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.

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: Stepwise plots

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/

### RE: Stepwise plots

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

TTFN
FAQ731-376: Eng-Tips.com Forum Policies
[IMG http://tinyurl.com/7ofakss]
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

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

### RE: Stepwise plots

As can Internet Explorer.

### RE: Stepwise plots

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

### RE: Stepwise plots

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

TTFN
FAQ731-376: Eng-Tips.com Forum Policies
[IMG http://tinyurl.com/7ofakss]
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

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

### RE: Stepwise plots

(OP)
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.

### RE: Stepwise plots

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


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
...


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)

B3: =IF($A3=$A2,INDEX(INDIRECT(B$1),MATCH($A3,INDIRECT($A$1),0),1),B2)

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: Stepwise plots

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!

### RE: Stepwise plots

(OP)
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.

www.mccoy.it

### RE: Stepwise plots

2
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/

### RE: Stepwise plots

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!