I'm sure there is a way...

[GMB_Excel]

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

[JE McGimpsey]

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 <[email protected]>,
GMB_Excel <[email protected]>
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?

[GMB_Excel]

This is just great! Thank you so much!

George

[GMB_Excel]

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

[JE McGimpsey]

One way:

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


In article <[email protected]>,
GMB_Excel <[email protected]>
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]

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

One way:

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


In article <[email protected]>,
GMB_Excel <[email protected]>
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

[Ardus Petus]

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" <[email protected]> a écrit
dans le message de
news:[email protected]...
>
> 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 <[email protected]>,
> > GMB_Excel <[email protected]>
> > 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
>