1

I have a .csv file and it contains thousands of rows. I collected this data file as output of running my program for 60 minutes. This output file contains time column (in forum HH:MM:SS:MS), this time column is recording time for my outputs. I want to get plot of my other columns in my output.csv file according to my time column (taking the results for all columns every 1 minutes).

For example:

I have a row like this: 

Data             Time
-----                 -----
455              10:00:00
894              10:00:00 
785              10:00:00
898              10:00:01
789              10:00:01
748              10:00:02
248              10:00:02
788              10:00:02
148              10:00:02
742              10:00:02
...              ...
266              10:01:00
...              ...

Is there any easy way to plot other columns with rows according to time column (taking the results for all columns every 1 minutes) ?

Community
  • 1
  • 1
medo0070
  • 511
  • 1
  • 5
  • 21
  • What do you mean by `monitor`? What do you expect the output to be? A result for every 15 minutes? If so, you will find [this related post helpful](http://stackoverflow.com/questions/30378085/search-column-a-to-match-a-number-in-column-c-if-true-return-column-b-if-false-r). – Byron Wall Jun 10 '15 at 22:28
  • I edited the post, by word monitoring i mean getting results from output. Expected output will "collected data per minutes". While i am running the program for 60 minutes, i need to get "data per minutes for first 15 minutes of my 60 minutes of running my program". – medo0070 Jun 11 '15 at 05:31

1 Answers1

3

While the question is not completely clear/consistent, I understand you want to count the number of data for each of the first 15 intervals

10:00 <= time < 10:01
etc.

For the first interval, you can use

=SUMPRODUCT(($B$2:$B$8>=TIME(10;0;0))*($B$2:$B$10000<TIME(10;1;0)))

I assume your time data is in B2:B10000. You can expand this range as needed, there is no problem in having an excess range (blank cells will not be counted). Or you could use

=SUMPRODUCT(($B:$B>=TIME(10;0;0))*($B:$B<TIME(10;1;0)))

You can easily create a column with the start time for each interval, and another column using (a modification of) this formula to for the data count.

Then you would plot the two columns just created.

sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
  • 1
    First of all thank you for your unswer. Yes you completely understand the problem. But i have a problem with time, the problem is the time **B2** is not fixed to **B10000** it is variable time and i don't know its end. It maybe **B10000** or maybe less or more. The second calculations of time will be from **B10000:B10055**. Is there any way to get calculate unstable time like this in excel ? – medo0070 Jun 11 '15 at 06:01
  • 2
    @medo0070 - See edited answer. The second interval would still use the same input range, that is what absolute referencing (using `$`) is for. – sancho.s ReinstateMonicaCellio Jun 11 '15 at 06:12
  • Thank you for your help it is helped me :) – medo0070 Jun 11 '15 at 07:42