Hello everyone!

This is my first visit here and I hope to get an answer on something I
am trying to get done in Excel. A look through other posts have also
made me realise how much more can be done in Excel than I ever
thought...

Here's my situation: I am a physiologist collecting data from an
ultrasound machine on the diameter of blood vessels. When my experiment
is finished I end up with a file that has four columns and up to 23,000
rows. The important columns are the first two and they are a time
column (format: hh:mm:ss), and a diameter column. The frequency of
measurements is such that for every second you end up with 25 rows, but
unfortunately, sometimes you end up with 24 rows in a second. What I
need to do is to get average diameters for every second and for every
ten seconds.

What I have done so far is to use "if" statements to give all the rows
within a second a marker (so that "10:25:01" is "1", "10:25:02" is "2",
etc.), and then average the diameters for each of these markers (using
more "if" statements to add the diameters within a marker and divide by
the "count" of the marker). Having done this I end up with a column
where I get the average diameters I need, but with 24 blank cells in
between. That's where the problems begin.

Problem 1: I get an error message saying that a 2D plot can only
contain up to 16,000 points for each series. This is despite filtering
the column with the results to only display the "nonblanks" which are a
lot less than 16,000.

Problem 2: When I average the one-second intervals to get ten-second
intervals, and then try to filter this column to only disply
"nonblanks" my averages are messed up because the source column has
changed as well.

My questions:

1. Is there any way to average for every so many rows? If yes, is there
a way to quickly have an automatic check to see if every second has 25
rows to it? If I cannot is there a way I can work around the
possibility of having a few seconds that will contain a different
number of rows?

2. When you have a column with blank cells, is it possible to copy it
and then paste only the nonblank cells? (this could prevent the error
message I get and which pauses my macro).

I appreciate this is a long, and possibly unclear explanation so I will
not be too surprised if you ignore me, but thanks in advance anyway.

All the very best,
George


--
GMB_Excel
------------------------------------------------------------------------
GMB_Excel's Profile: http://www.excelforum.com/member.php...o&userid=32141
View this thread: http://www.excelforum.com/showthread...hreadid=518979

This seems a perfect application for a Pivot Table.

http://peltiertech.com/Excel/Pivots/pivotstart.htm

If you make your data field the Average of diameters (rather than the
Sum), the PT will by default average by second, regardless of the number
of data points in each second.


In article ,
GMB_Excel
wrote:

1. Is there any way to average for every so many rows? If yes, is there
a way to quickly have an automatic check to see if every second has 25
rows to it? If I cannot is there a way I can work around the
possibility of having a few seconds that will contain a different
number of rows?

This is just great! Thank you so much!

George


--
GMB_Excel
------------------------------------------------------------------------
GMB_Excel's Profile: http://www.excelforum.com/member.php...o&userid=32141
View this thread: http://www.excelforum.com/showthread...hreadid=518979

OK, here comes another question to follow up from the previous one.

If you write a macro and you need to specify a range of cells that
might change between different files, can you actually specify a range
that goes from your starting cell to the "end" (last nonblank cell in
range)?

George


--
GMB_Excel
------------------------------------------------------------------------
GMB_Excel's Profile: http://www.excelforum.com/member.php...o&userid=32141
View this thread: http://www.excelforum.com/showthread...hreadid=518979

One way:

Dim rRng As Range
Set rRng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)


In article ,
GMB_Excel
wrote:

OK, here comes another question to follow up from the previous one.

If you write a macro and you need to specify a range of cells that
might change between different files, can you actually specify a range
that goes from your starting cell to the "end" (last nonblank cell in
range)?

George

OK, thanks. But how do I use this if I don't write my macro in visual
basic? I only know how to record a macro and then edit it (to a certain
extent). So, this is what my macro looks like (part of it) when I copy a
formula to the whole column by using the autofill function:

Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:B3"),
Type:=xlFillDefault
Range("A3:B3").Select
Rows("4:4").Select
Selection.Delete Shift:=xlUp
Range("B4").Select
ActiveCell.FormulaR1C1 = "1"
Range("B5").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]-R[-1]C[-1]=0,R[-1]C,R[-1]C+1)"
Range("B5").Select
Selection.AutoFill Destination:=Range("B5:B22135")
Range("B5:B22135").Select


It is the "if" statement that I want to copy to the end of my data. How
would I modify this macro to do it?

Thanks a lot for your help.

George




JE McGimpsey Wrote:
One way:

Dim rRng As Range
Set rRng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)


In article ,
GMB_Excel
wrote:

OK, here comes another question to follow up from the previous one.

If you write a macro and you need to specify a range of cells that
might change between different files, can you actually specify a
range
that goes from your starting cell to the "end" (last nonblank cell
in
range)?

George


--
GMB_Excel
------------------------------------------------------------------------
GMB_Excel's Profile: http://www.excelforum.com/member.php...o&userid=32141
View this thread: http://www.excelforum.com/showthread...hreadid=518979

Near the end of your Sub, the following lines:
'------------------------
Selection.AutoFill Destination:=Range("B5:B22135")
Range("B5:B22135").Select

'------------------------
becomes:
'------------------------
Selection.AutoFill Destination:=Range("B5",Cells(Rows.count,"B").end( xlup))
'------------------------

(you can delete the "Range("B5:B22135").Select" line

HTH
--
AP
"GMB_Excel" a écrit
dans le message de
...

OK, thanks. But how do I use this if I don't write my macro in visual
basic? I only know how to record a macro and then edit it (to a certain
extent). So, this is what my macro looks like (part of it) when I copy a
formula to the whole column by using the autofill function:

Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "General"
Range("A3").Select
Selection.AutoFill Destination:=Range("A3:B3"),
Type:=xlFillDefault
Range("A3:B3").Select
Rows("4:4").Select
Selection.Delete Shift:=xlUp
Range("B4").Select
ActiveCell.FormulaR1C1 = "1"
Range("B5").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-1]-R[-1]C[-1]=0,R[-1]C,R[-1]C+1)"
Range("B5").Select
Selection.AutoFill Destination:=Range("B5:B22135")
Range("B5:B22135").Select


It is the "if" statement that I want to copy to the end of my data. How
would I modify this macro to do it?

Thanks a lot for your help.

George




JE McGimpsey Wrote:
One way:

Dim rRng As Range
Set rRng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)


In article ,
GMB_Excel
wrote:

OK, here comes another question to follow up from the previous one.

If you write a macro and you need to specify a range of cells that
might change between different files, can you actually specify a
range
that goes from your starting cell to the "end" (last nonblank cell
in
range)?

George


--
GMB_Excel
------------------------------------------------------------------------
GMB_Excel's Profile:
http://www.excelforum.com/member.php...o&userid=32141
View this thread: http://www.excelforum.com/showthread...hreadid=518979