graphical representation of pulses 1

[BlackSeal]

Hi All,

Could someone give me some pointer on howto represent the following graphically:
7.5
726
25.75
641
25.75
40.75
25.5
41.25
25.5
41
25.5
These are values in milliseconds and the format is alwyas the same, the first value of the list is a puls duration, and the next value is the duration till the next pulse. (so pulse-interval-pulse-interval).

I would really like to represent this graphically, can oneone help howto write a macro for this? Thanks very much.

 

[onlyadrafter]

Hello,

This code does need some more work, but it creates a graph of the values given. If you add any more or remove some the code is not quite right. I have spent quite a bit of time on this and thought you may want to see the results so far.

Is this any good?

Dim MY_VALUES(1000) As Variant
Dim ADD_VALUES(1000) As Variant
Sub Macro1()
LATEST = 0
TOTAL = 0
MOVE_OVER = 0
PULSE = 0

Columns("E:G&quot.ClearContents
For MY_VALUE = 1 To Range("a65536&quot.End(xlUp).Row
MY_VALUES(MY_VALUE) = MY_VALUES(MY_VALUE) + Range("A" & MY_VALUE).Value
Range("E65536&quot.End(xlUp).Offset(1, 0).Value = MY_VALUES(MY_VALUE)
Range("E65536&quot.End(xlUp).Offset(1, 0).Value = MY_VALUES(MY_VALUE)
If PULSE = 0 Then
PULSE = 1
Else
PULSE = 0
End If
Range("F65536&quot.End(xlUp).Offset(1, 0).Value = PULSE
Range("F65536&quot.End(xlUp).Offset(1, 0).Value = PULSE
MOVE_OVER = MOVE_OVER + 2
Next MY_VALUE

MOVE_OVER = 1
Range("G2&quot.Value = MY_VALUES(1)
TOTAL = MY_VALUES(1)
For ADD_VALUE = 1 To Range("E65536&quot.End(xlUp).Row - 2
TOTAL = TOTAL + Range("e1&quot.Offset(MOVE_OVER, 0).Value
Range("G65536&quot.End(xlUp).Offset(1, 0).Value = TOTAL
MOVE_OVER = MOVE_OVER + 1
Next ADD_VALUE
A_VALUES = "=Sheet2!R2C7:R23C7"
B_VALUES = "=Sheet2!R2C7:R23C7"
C_VALUES = "=Sheet2!R2C6:R23C6"
Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets("Sheet2&quot.Range("F2:F23&quot, PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).Values = A_VALUES '"=Sheet2!R2C7:R23C7"
ActiveChart.SeriesCollection(1).XValues = B_VALUES '=Sheet2!R2C7:R23C7"
ActiveChart.SeriesCollection(1).Values = C_VALUES '"=Sheet2!R2C6:R23C6"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
ActiveChart.HasLegend = False
End Sub


----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[BlackSeal]

Thanks, I'll give it a try, I'll let you know if it works. Many thanks for now.

 

[BlackSeal]

Hi onlyadrafter,

I give it a try, and after some modifications I got a chart that looks the way that I wanted. This is how the macro works for me:

Option Explicit
Dim LATEST, TOTAL, MOVE_OVER, PULSE, MY_VALUE, ADD_VALUE As Variant
Dim A_VALUES, B_VALUES, C_VALUES As Variant
Dim MY_VALUES(1000) As Variant
Dim ADD_VALUES(1000) As Variant
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+s
'
LATEST = 0
TOTAL = 0
MOVE_OVER = 0
PULSE = 0

Columns("E:G&quot.ClearContents
For MY_VALUE = 1 To Range("a65536&quot.End(xlUp).Row
'MY_VALUES(MY_VALUE) = MY_VALUES(MY_VALUE) + Range("A" & MY_VALUE).Value
MY_VALUES(MY_VALUE) = Range("A" & MY_VALUE).Value
Range("E65536&quot.End(xlUp).Offset(1, 0).Value = MY_VALUES(MY_VALUE)
'Range("E65536&quot.End(xlUp).Offset(1, 0).Value = MY_VALUES(MY_VALUE)
If PULSE = 0 Then
PULSE = 1
Else
PULSE = 0
End If
Range("F65536&quot.End(xlUp).Offset(1, 0).Value = PULSE
'Range("F65536&quot.End(xlUp).Offset(1, 0).Value = PULSE
MOVE_OVER = MOVE_OVER + 2
Next MY_VALUE

MOVE_OVER = 2
Range("G2&quot.Value = MY_VALUES(1)
TOTAL = MY_VALUES(1)
For ADD_VALUE = 1 To Range("E65536&quot.End(xlUp).Row - 2
TOTAL = TOTAL + Range("e1&quot.Offset(MOVE_OVER, 0).Value
Range("G65536&quot.End(xlUp).Offset(1, 0).Value = TOTAL
MOVE_OVER = MOVE_OVER + 1
Next ADD_VALUE

A_VALUES = "=Sheet2!R2C7:R313C7"
B_VALUES = "=Sheet2!R2C7:R313C7"
C_VALUES = "=Sheet2!R2C6:R313C6"

Charts.Add
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Sheets("Sheet2&quot.Range("F2:F313&quot, PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Values = A_VALUES
ActiveChart.SeriesCollection(1).XValues = B_VALUES
ActiveChart.SeriesCollection(1).Values = C_VALUES
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
ActiveChart.HasLegend = False
End Sub

=======================================================
The lines that I commented out, why did you repeat them?

 

[onlyadrafter]

Hello,

Those lines are repeated because I thought that you wanted the graph to show the length of the pulse, so instead of the graph going straight up and down, it goes up along a bit, then down, than along a bit and so on.

But I'm glad I was of some help.

As I said before, if you change then number of values then these lines will need some work on them

A_VALUES = "=Sheet2!R2C7:R23C7"
B_VALUES = "=Sheet2!R2C7:R23C7"
C_VALUES = "=Sheet2!R2C6:R23C6"



----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[BlackSeal]

I se what you mean, I was not looking at the graph correctly, this is what I wanted. Many thanks!

 

[jabaldauf]

I choose to do this without a macro. To do this, I need to follow each of the times with a zero. If you have a large number of data points, you may want to write a small macro to do that. Let's say all these durations and zeros are in column A.

Then in column B I calculate the total time by adding the duration from column A to the previous total time in the cell above (column B).

Then in column C I copy the pattern 1, 0 so that C1=1, C2=0, C3=1, C4=0, etc. Chart columns B and C.

Hope this is useful.

-Jim