Tripartite Plots

[jksmurf]

Hi, new user, very green, so please bear with me

I am trying to plot peak ground motions from construction blasting data to predict principal frequencies, following Charles H Dowding's excellent text "Blast Vibration Monitoring and Control".

The resultant formulas give me a (calculated) Maximum Velocity, Acceleration and Displacement. Plotting these maxima (for all freqeuncies) should result in an inverted V shaped graph (with a flat at the top of the V), with the principal frequency at the top or "point" of the V.

How do I go about plotting this in DPLOT? The DPLOT help says "X values = Frequency in hertz, Y values = velocity in inches/sec ...etc.) However I am trying to DETERMINE frequency, not plot it, vs. Peak Velocity.

I know I could set up a full spectrum of frequencies and plot the ONE Velocity value against it, but this is only the flat top part of the curve. The legs of the V cannot be plotted as (AFAIK) you cannot PLOT Displacement and acceleration, it is just shown as two extra diagonal lines on a LOG-LOG plot.

Is there a way to plot this? Would it be possible to have all FOUR indices in Excel Columns (e.g. change XYYY to XYZA), and choose to plot just ANY PAIR of them (e.g. XY, XZ, XA, YZ, YA, ZA ..) so the other two could be determined?

btw, small request and with all due respect to the developers who have made a really fine program, I find the drop-down menu add-in to Excel is a little rudimentary. Just having XYXY or XYYY are a little confusing, if the DPLOT fellows could perhaps annotate which one is suggested for use with which graph type e.g. "Use this one for Tripartite Graphs.. " ?

[DPlotAdmin]

Search the Help file for "tripartite", then click the "Units" link in the "tripartite grids" paragraph. There you'll see how velocity, displacement, acceleration, and frequency are related. Given any two of those parameters you can find the other two.

d=v/(2*pi*f)
a=2*pi*f*v

so if you know displacement (d) and velocity (v),

f = v/(2*pi*d)

if you know acceleration and velocity,

f = a/(2*pi*v)

if you know acceleration and displacement,

v = d*2*pi*f = a/(2*pi*f); f = 2*pi*sqrt(a/d)

btw, small request and with all due respect to the developers who have made a really fine program, I find the drop-down menu add-in to Excel is a little rudimentary. Just having XYXY or XYYY are a little confusing, if the DPLOT fellows could perhaps annotate which one is suggested for use with which graph type e.g. "Use this one for Tripartite Graphs.. "

Well... XYXY vs. XYYY really doesn't have anything to do with what type of scaling is used (standard linear X, linear Y is always used), only with how the data is organized. A shock spectra plot might be either one. XYXY is for alternating X and Y columns, where the X values for each curve are unique. XYYY is for one column of X values that is shared by all curves. If you only have one curve (2 columns) then XYXY and XYYY are equivalent.

[jksmurf]

That was quick!

Thank you very much, I think that will do it nicely, by just adding a new calculation and plotting Freq vs Peak velocity.

For the Excel Plugin comment, it was simply a case of explanation of what I was seeing in Excel leaving me a bit confused (not that that is difficult
Cheers and thanks again,
k.

[jksmurf]

Right, I got a graph by performing the requisite calculations to determine frequency for known pairs of the other 3 indices, so thanks for that, you were spot on

I have another request, peculiar to what I am plotting, which may or may not be possible. The calculations for each inverted (flat top) V-Shaped graph result in 4 pairs of ordinates. These 4 pairs are in 8 columns in Excel i.e. X1Y1, X2Y2, X3Y3, X4Y4. I have about 400 of these rows, i.e. I want to plot as many graphs (each with 4 points) as possible.

To graph them on the Tripartite Graph (if I am doing this right), I have had to put transpose data from the 8 columns into two columns, choose the Dplot addin and get the preferences, presto, nice plot, but of course, this is only one.
X1 Y1
X2 Y2
X3 Y3
X4 Y4

What I would REALLY like to do, so I do nto have to transpose the data (which is also a possibility, but not the preferred one) is to have Dplot recognize each graph (i.e. each row) is made up of four ordinates, but each graph is made up of teh 4 pairs on one ROW, like this.

Plot1 1=> X1aY1a, X2aY2a, X3aY3a, X4aY4a.
Plot1 2=> X1bY1b, X2bY2b, X3bY3b, X4bY4b.
Plot1 3=> X1cY1c, X2cY2c, X3cY3c, X4cY4c.
etc.

Would this be possible?
Thanks!

Kristian

[DPlotAdmin]

If you're feeling adventurous...

In Excel, select Tools>Macro>Visual Basic Editor. In the "Project" pane (by default in the upper left corner), click on "DPlotLib (dplotlib.xla)". You will be prompted for a password. Type "dplot". Back in the Project pane, click the + next to "Modules". Double-click on "DPlotData". Scroll to the end of the file and paste (press Ctrl+V) the following:

Code: Select all

Sub OneCurvePerRow()
'
'   On entry, the selection should be a table of alternating X,Y pairs. Each row is considered a
'   separate curve.
'
    Dim fmt As Integer
    Dim Doc As Long
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim nColumns As Long
    Dim nRows As Long
    Dim cmds As String
    Dim ret As Long
    Dim Sel As Object
    Dim TotalPoints As Long
    Dim NP As Long
    Dim X() As Double
    Dim Y() As Double
'
' Change drive and directory to the location of this file (and, hopefully,
' the drive and directory where DPLOTLIB.DLL is located. Otherwise Excel won't
' find DPLOTLIB.DLL unless we place it in the Windows System folder.
'
    ChDrive ThisWorkbook.Path
    ChDir ThisWorkbook.Path
    
    Set Sel = Selection
    nColumns = Sel.Columns.Count
    nRows = Sel.Rows.Count
    If nColumns Mod 2 = 1 Then
        MsgBox "The selection has an odd number of columns."
        Exit Sub
    End If
    If nRows > 100 Then
        MsgBox "The number of rows in the selection should be no greater than 100."
        Exit Sub
    End If
    
    NP = nColumns / 2
    TotalPoints = NP * nRows
    ReDim X(TotalPoints - 1)
    ReDim Y(TotalPoints - 1)
    
    k = 0
    For i = 1 To nRows
        D.NP(i - 1) = NP
        D.LineType(i - 1) = LINESTYLE_SOLID
        For j = 1 To nColumns - 1 Step 2
            X(k) = Sel.Cells(i, j)
            Y(k) = Sel.Cells(i, j + 1)
            k = k + 1
        Next j
    Next i
        
    D.Version = DPLOT_DDE_VERSION
    D.DataFormat = DATA_XYXY
    D.MaxCurves = nRows
    D.MaxPoints = NP
    D.NumCurves = nRows
    D.ScaleCode = SCALE_LINEARX_LINEARY
    D.Title1 = ActiveWorkbook.Name
    D.XAxis = "x"
    D.YAxis = "y"
    cmds = "[Caption(" & Chr$(34) & Trim$(D.Title1) & Chr$(34) & ")]"
    fmt = DPlotNumberFormat(Sel.Cells(1, 1).NumberFormat)
    If fmt <> NF_DEFAULT Then
        cmds = cmds & "[NumberFormat(0," & Str$(fmt) & ")]"
    End If
    fmt = DPlotNumberFormat(Sel.Cells(2, 1).NumberFormat)
    If fmt <> NF_DEFAULT Then
        cmds = cmds & "[NumberFormat(1," & Str$(fmt) & ")]"
    End If
    Doc = DPlot_Plot8(D, X(0), Y(0), cmds)
    ret = DPlot_Command(Doc, "[SetWindowPos(0,0,0,0,0,0,3)]")   ' Bring DPlot to top
    
End Sub

You may want to change SCALE_LINEARX_LINEARY to SCALE_TRIPARTITE.

Back in the Project pane, open "Microsoft Excel Objects", then double-click "ThisWorkbook". At the bottom of the subroutine Workbook_Open, before the line "End Sub", paste this:

Code: Select all

    Set ctrl = newMenu.Controls.Add(Type:=msoControlButton)
    ctrl.Caption = "OneCurvePerRow"
    ctrl.Style = msoButtonCaption
    ctrl.OnAction = "OneCurvePerRow"

Click the "Save" button.

The 4 lines above will add a command to run OneCurvePerRow to the DPlot menu, but not until you exit and restart Excel.

I'll dress this thing up a bit more before the next release and make it a bit more flexible.

[jksmurf]

Thank you. You are just awesomeNESS!

Shame I can't attach files to show how GREAT this works, but I sent you an email with the plot. 100 Plot limit, but that'll do just fine.

Cheers and THANK YOU!

k.

btw1. I didn't change SCALE_LINEARX_LINEARY to SCALE_TRIPARTITE, would it be ONLY for Tripartite Plots, if not then I guess no drama what it is called!
btw2. There was no "Microsoft Excel Objects"??? in the Projects Section but there was a "ThisWorkbook" under the Dplot Section of Projects. I assumed it was correct as the previous commands were all there (Just in case anyone else is attempting this.)

[DPlotAdmin]

You're quite welcome. Thanks for the suggestion.

btw1. I didn't change SCALE_LINEARX_LINEARY to SCALE_TRIPARTITE, would it be ONLY for Tripartite Plots, if not then I guess no drama what it is called!

In the Add-In I'll leave it linear X, linear Y, since it's just a right mouse click to change it to whatever you want within DPlot. The only harm in setting SCALE_TRIPARTITE within the Add-In is that if you have any negative values you'll get an error message rather than a plot, even though you might have intended to reset the scale type immediately within DPlot.

btw2. There was no "Microsoft Excel Objects"??? in the Projects Section but there was a "ThisWorkbook" under the Dplot Section of Projects. I assumed it was correct as the previous commands were all there (Just in case anyone else is attempting this.)

Thanks for the info. My guess is this is an Excel version-dependent issue. Glad you figured it out.