Using last cell in row in the range of a formula

[jeremydmoore]

Hello,

I found this board and after some searching I either couldn't find what would help me or if I did, I wasn't sure exactly how to use it. To speed up my work in the lab I wanted to create a formula that could do the standard deviation and coefficient of variation for a set of data, but I don't know where to start.

The data is outputted from a separate program directly into excel and the number of columns differ based on the number of channels recorded (I work in a lifescience lab and we are recording wave shapes). I can choose in which column the data starts in and it always starts in row 2.

What I need is something like this where for example I have chosen the data to start in column D. The problem with manually selecting this is that I can have anywhere from 32-256 channels (columns) and the number of rows is also usually quite high (250+) so I wanted to create a macro to cut down on the time I spend doing this manually.

=STDEV(D2:*Last column with data in it*)

and

=STDEV(D2:*Last column with data in it*)/Average(D2:*Last column with data in it*)

Is there any simple way to do this?

 

[erik.van.geit]

Hello, jeremydmoore,
Welcome to the Board !!!

last column with data in row 2 found with VBA is

R = 2
LC = Cells(R, 256).End(xlToLeft).Column

you can do this with formulas
to find numeric input
=MATCH(9.99999999999999E+307,2:2)
to find strings
=MATCH(REPT("z",255),2:2)

does this help ?

kind regards,
Erik