Plotting - take 5 values - average - every two hours - file name changes daily

  • Thread starter Thread starter ageorgm
  • Start date Start date
A

ageorgm

Good Day, I am trying to come up with an automated way to retrieve data
every two hours; average 5 samples then plot a point for each of the
columns for a tester that creates a .csv file. The file name change
daily, as in 9-1-04.csv, 9-2-04.csv, etc.
The data needs to be placed in a chart format, but doesn't need to b
in the same .csv file. I also need it to bypass any data that is les
than 0 (example: -1) and grab the next value under it to complete the
samples. Can I have a macro do this for me? I highlighted all th
respective columns, I would need the time value to show when the dat
was collected, maybe system time, or use the last time value of th
subgroup. I have attached the file, with proprietary informatio
removed. Thanks for any help. Jef

Attachment filename: 09-01-04.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=66762
 
If you have a 'summary' workbook, you can do what you want with minimal
VBA support.

In the summary workbook:
Enter, in A1: Date of interest
In B1: File of interest
In C1:D1, Col1 and col2 respectively. Use additional columns for other
columns you want analyzed.

In A2, enter =TEXT(NOW(),"mm-dd-yy")
In B2, enter ="'["&A2&".csv]"&A2&"'!"
In C2, enter n1:n65535
In D2, enter p1:p65535
Enter additional column references in E2:G2 as needed.

In C3 enter the *array formula*
=AVERAGE(IF(INDIRECT($B$2&C2)>0,INDIRECT($B$2&C2)))

Copy C3 to D3, or D3:G3 as appropriate.

Save this file in the same folder as where the daily .csv files exist.

Note that nothing in the summary file requires programmatic support.

Open this file and open the .csv file for today. This is the only
piece that needs automation.

Note that while XL has the csv file open, whatever other process
updates it may fail to do so.

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Thank you very much, I have applied the formulas, and it works great
Instead of using n1:n65535, I used n1:n10 then added another row o
code, and used n30:n40 (every 20 entries is 2 hours) and so on t
capture the every two hours. One question though, is this grabing al
10 samples then averaging them
=AVERAGE(IF(INDIRECT($B$2&C2)>0,INDIRECT($B$2&C2)))
How can I tell it to only use 5 samples? I allow for the 10, in case i
comes to >0, then it bumps to the next good value. I also had it gra
the time for the last value for the axis.
I put this in a templete format, then all we have to do it change th
system date to what ever dated .csv file we want to plot, and graphe
the 5 areas to it's own sheet within the summary book. I then added V
code to autosave with a timestamp to the directory of my choice. Now i
I can come up with something to auto run, we would be 100% automated.
Thanks again, Jef
 
Back
Top