Formula: Std Dev

Hi ..

I need some help urgently to get the Std Dev in excel spreadsheet.

I have tried to record the macro but not really understand it.

ActiveCell.FormulaR1C1 = "=STDEV(R[-20]C:R[-11]C)"


In the excelspreadsheet, the row of data might not be fixed.
Example: 1st excelsheet, the total record is 20 records which is from C1 to C20
Example: 2nd excelsheet, the total record is 100 records which is from C1 to C100

My question is how am i going to write the VB script in order to get the Std Dev based on the total record in excelspreadsheet?


Thanks.



Please post Excel questions, in the Excel forum.

Thank you.

I think you would find it easier to use A1 reference style instead of R1C1 reference style? To understand the formula as it stands check out the "About cell and range references" (R1C1 reference style section) in your Excel helpfile.

The most common way people use to determine the last cell in a column is to use the Range Object's End Property. This approach makes a number of assumptions such as that the last cell is not hidden. Check it out in the VBA helpfile or try a search in this board where there are thousands of examples.

Hi Colin_L,

Thanks for the advise.

I have done it using the script.