EXCEL (macro) - Analysis of hourly intervals from sectional graph, correlation

[jezurka]

Hello guys,

my problem is about making analysis of hourly intervals from sectional graph (like mean, min, max) and then subsequent correlation among columns D-F in table. Graph is made from table.

I started with MATLAB, where i made application, which exports data into Excel in form of table and then in VBA I made macros for creating graphs from table.

Below u can see current results.

Sectional graph is made like when u click in the column "C" on some time. e.g.: 19:00. The graph will show one hour before this time and 2 hours after.

tabulka_excel.jpg

-> Table

Cely graf.jpg

-> Graph made from whole table

usek.jpg

-> Sectional graph made from table, when i clicked 19:00. So it shows 1 hour before and 2 hours after.

visual.jpg

-> Code in Visual Basics for making sectional graph

So i just need to make analysis from sectional graph..The best thing will be to insert it into macro for sectional graph. But i dont know how. I cant using VBA at all.
Thank u very much.

[p45cal]

No-one, but no-one will help you from just pictures - no-one's going to type out your code from a picture.
Supply an Excel file with all that.

[jezurka]

I am sorry, i attached file of excel and below code for sectional graph. is it right now?

Sub Úsek_grafu()

Dim radky As Integer
Dim bunka_start As String
Dim bunka_konec As String
Dim pocatek As Integer
Dim index_start, index_konec As Integer
'zvoleny radek
pocatek = ActiveCell.Row
'zjisteni pocet radku
For a = 3 To 65555
If Cells(a, 3) =  Then
radky = a - 1
Exit For
End If
Next
'omezeni pro zacatek a konec grafu
If pocatek  60 Then
index_start = 3
Else
index_start = pocatek - 60
End If
If pocatek + 120  radky Then
index_konec = radky
Else
index_konec = pocatek + 120
End If

'prevede na format A1
bunka_start = Cells(index_start, 3).Address(RowAbsolute=False, ColumnAbsolute=False)
bunka_konec = Cells(index_konec, 8).Address(RowAbsolute=False, ColumnAbsolute=False)

'vytvori graf
'
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source=Range(bunka_start &  & bunka_konec)

End Sub

Thank you very much.

[p45cal]

I am sorry, i attached file of excel and below code for sectional graph. is it right now?

Not really. The following lines have been corrupted and your file contains no vba code (it refers to code in your Personal.xlsb file which is on your computer.
If Cells(a, 3) = Then
If pocatek 60 Then
If pocatek + 120 radky Then
ActiveChart.SetSourceData Source=Range(bunka_start & & bunka_konec)

Copy/Move your code to your vyhodnoceni_graf.xlsm file and attach it again

More important. You need to provide a link to any cross-posts. You need to read the rules BEFORE you post.

[jezurka]

Ye, i see it. I hope i made it right.
Thank you.

[p45cal]

Ye, i see it. I hope i made it right.

Almost; again,

More important. You need to provide a link to any cross-posts. You need to read the rules BEFORE you post.

1.

The graph will show one hour before this time and 2 hours after.

No it won't. It'll show up to 60 readings before and up to 120 reading after your chosen cell. If that cell is 23:00, then that will include readings taken 6 hours previously. Will you get meaningful statisitics from that?
Subject to your approval, I have changed the chart drawing code to include up to 1 hour earlier and up to 2 hours after your chosen time.
You can see in the pictures below 3 charts. The red dotted vertical line in each one is the chosen time. You can see that there is not always 1 hour before and 2 hours after.
The first chart is normal. The 2nd chart does not have 1 hour's data before the chosen time because there isn't any data for that time. The 3rd chart doesn't have data for 2 hours after the chosen tme for that time.
Is it OK to use these charted times to produce your statistics?
2018-01-07_173858.jpg

2. Do you only want statistics for column D:F, not G:H too?

3. Before your data get into MATLAB, does the date/time stamp come as one, that is, date and time together, for example like 03/10/2014 17:07 rather than 03/10/2014 in one field and 17:07 in another? It would be very helpful if it did.

4. I will provide no further assistance here until cross-post link(s) have been provided.

[jezurka]

1. Ye, I understand you, but MATLAB generates always 2 types of datas. One is from vital function monitor and the another one is NIRS machine and put it together. The code in matlab is written in the way of divide data from each day in own list. So, it went smthing wrong that 2 days are on same list. I generated data again and it works right. I attached new excel file with macros of course. So I think in file like this, the sectional graph works right, doesnt it?

2. It will be nice to provide statistics from column D:H.

3. Data was in format .csv before i load it in matlab and devide it into columns. So it was separated by ";".

4. This is for me main forum. Other forum where i put this problem is: (i can´t post links even, post count is momentally 3 and I need 5), how should i solve it?

Thank u very much for your time. I appraciate it.

[offthelip]

you providethe link information by typing it in even if it isn't a link eg.
w w w .vbaexpress.com/forum thread 61706

[p45cal]

…or you post again explaining that you're posting to get your post count up to allow you to add working links (later a moderator might delete such posts once you're above 5 posts)

[jezurka]

This is for me main forum. Other forum where I put this problem: w w w.excelforum.com/excel-programming-vba-macros/1214507-excel-macro-analysis-of-hourly-intervals-from-sectional-graph-correlation.html#post4815898

I made a final version in list of excel. How it should look. But I need to make it "automatically" by macros. So is it possible to insert somehow into sectional graph macro to create min,max,median and box graphs together with sectional graph? The sectional graph is chosen in this case like in time 3:00. So it takes one hour before (2:00) and 2 hours after (5:00). From this, statistical analyze is doing.

On the other list, correlation of data is made, but the same problem. I just need to make it "more automatically". Data, which are generated by MATLAB haven´t the same lenght. It is the problem too.

Thank u for your time, I appreciate it.

[p45cal]

I won't be able to look at this now until Wednesday.

[jezurka]

Ye, I understand ) I will wait for your answer.

Thank you very much.

[p45cal]

regarding statistics, if values are zero (there are many) do you want them included in the stats? They will give misleading results.
In the case of RESP, you have one reading of -2 ! (row 230). Do you want this sort of negative number to be excluded from the stats?

[jezurka]

You are right. These values shouldn´t be included. But is it possible to make it without them? So we need the macro to be able to recognize zeros values, right?

[p45cal]

You are right. These values shouldn´t be included. But is it possible to make it without them? So we need the macro to be able to recognize zeros values, right?

I should be able to deal with that.
Currently working on the statistics when selected cell is variously: the first cell of the data, the last cell of the data, when there's not a complete hour before the cell, when there's not a complete hour after the cell, when there's not a full 2 hours after the cell etc. etc.
So I haven't forgotten you.

[p45cal]

Have a look at sheet List1 (2) of the attached.
You can click the button or press Ctrl+i on the keyboard after selecting a cell amongst the data.
It assumes quite a number of things including:

• data starts at row 3
• sorted by time ascending
• times in column C
• works on the active sheet

Chart gets plotted at the top of the sheet, stats written to cell J23

Check the statistics are correct.

[jezurka]

Ouuu, thanks a lot. It seems pretty nice!

Last question, if i may..Is it even possible to make box graphs for each hour (so 3 graphs) from the statistics table, which u create in cell J23, and add it into the macro u made?
This is my last request.

Thank you for your time..It means a lot for me.

[p45cal]

See attached.

[jezurka]

Thank you very much for everything!

[CRYPTOMART]

Good job