How to calculate the mean and standard deviation of a dynamic column by VBA RRS feed

  • Question

  • Hey,

    I'm trying to calculate the mean and standard deviation of a column. This column doesn't have a fix position. If I give a value N, it is in Nth column, from row 0 to row N. I have thought about using Application.WorksheetFunction.Average(...) and Application.WorksheetFunction.StDev(...), but I don't know what to write in (...)

    Appreciate any help!

    Wednesday, November 6, 2013 5:30 PM

All replies

  • You can use


    (Excel starts counting at 1 so Columns(1) corresponds to column A, Columns(2) to column B etc.)

    Regards, Hans Vogelaar (

    Wednesday, November 6, 2013 9:04 PM
  • Hi,

    You can also replace N with the column name for example for column B write:

    application.WorksheetFunction.Average(columns("B")) or use the the column number application.WorksheetFunction.Average(columns(2))

    Guy Zommer

    Thursday, November 7, 2013 10:33 AM