PLOTING LARGE DATA

[mitch]

All:
I am trying to plot huge data file for pressure, volume & temperature with respect to time and was wondering if this can be simlified following way:

For a pressure difference of 100 psi I can take corresponding values of temperature, volume and time. Time is the x axis and the other 3 will be ploted against y axis.

we are talking 1000,000,000 cell data. Thus I want to polt only few hunderd points only.

Thanks,

 

[pmover]

um, lets see...
i do not believe it possible for excel to handle that many data points - not positively certain though - even though there are > 63,000 rows and 256 columns. it would be difficult to plot though, even if all the data were in one sheet!

if you have the original data file, perhaps you can write a little vba code to conduct some method of averaging the original data (i.e. a running average) or to select every 100th data point.

another suggestion would be to break the original data file into small chunks of data, say like 50,000 data points and then generate a plot as you desribed. even a plot of that size may generate errors or take a little time to generate, depending upon the pc capabilities. you would then create plots for all the data, but that would mean 2000 or so plots - too much work.

i'd consider writing vba code to conduct a statistical analysis (i.e. mean, median, max, min, std deviation, etc.) and generate a histogram on your data. once you've obtained this information, perhaps further analysis can be conducted by eliminating (not using) certain portions of the data.

just what in the heck is it that you are doing that contains that much data?

i've done several recip pv card analysis using xl that generated voluminous amounts of data, but not that much!
just curious!
good luck and find your happy place!
-pmover

 

[krossview]

Mitch,

Treat the file as a standalone data file and read the file using the VB "OPEN" statement as a text, binary or perhaps random file, depending on your file format. From this point onward you can do whatever want as you read the file one line or record at a time.

The following example assumes file is binary as its the fastest way to read files

Type LineRecord
TimeStamp As Double
Press As Single
Volume As Single
Temp As Single
End Type

Dim TextLine As LineRecord

FreeFileNum = FreeFile
Open "FileName" For Binary As #FreeFileNum

' Read 1st record and get 1st pressure point
Get #FreeFileNum, , TextLine
P1 = TextLine.Press

Do While Not EOF(FreeFileNum)
'Read next line and calc Pressure drop
Get #FreeFileNum, , TextLine
dP = TextLine.Press - P1
If dP >= 100 Then
' Dump data into excel
nRow = nRow + 1
Cell(nRow, 1) = TextLine.TimeStamp
Cell(nRow, 2) = TextLine.Press
Cell(nRow, 3) = TextLine.Temp
Cell(nRow, 4) = TextLine.Volume
' Save last pressure point
P1 = TextLine.Press
End If
Loop
Close #FreeFileNum

Find out and experiment (time it) what file format works best for you as you can save yourself hours, if this is a frequent operation. You can do the same if the file is an ASCII text file, but you will need to do some more work on getting variables split out.

Note, using LINE INPUT # to read such a large text file may be quite slow that's why binary is preferred plus you can do more tricks with it.

Good Luck

Krossview/OK