Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 21

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

  1. #1

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

    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.
    Attached Images Attached Images
    Last edited by jezurka; 01-06-2018 at 04:37 PM.

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    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.
    Attached Files Attached Files
    Last edited by jezurka; 01-07-2018 at 04:12 AM.

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    Quote Originally Posted by jezurka View Post
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Ye, i see it. I hope i made it right.
    Thank you.
    Attached Files Attached Files

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    Quote Originally Posted by jezurka View Post
    Ye, i see it. I hope i made it right.
    Almost; again,
    Quote Originally Posted by p45cal View Post
    More important. You need to provide a link to any cross-posts. You need to read the rules BEFORE you post.



    1.
    Quote Originally Posted by jezurka View Post
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    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.
    Attached Files Attached Files

  8. #8
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    you providethe link information by typing it in even if it isn't a link eg.
    w w w .vbaexpress.com/forum thread 61706

  9. #9
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    …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)
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  10. #10
    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.
    Attached Files Attached Files

  11. #11
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    I won't be able to look at this now until Wednesday.

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

    Thank you very much.

  13. #13
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    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?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  14. #14
    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?

  15. #15
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    Quote Originally Posted by jezurka View Post
    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
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  16. #16
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    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.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

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

  18. #18
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,924
    See attached.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  19. #19
    Thank you very much for everything!

  20. #20
    Good job

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •