Excel Determine Range

Question

I am creating a spreadsheet which has a column of data, and I would like to calculate the varaince of x number of rows based on x inputed. Any advice?

Answer 1

Please look at the VAR and INDIRECT functions, then come back with a more specific question (and preferably an example). I am not convinced this requires VBA.

Answer 2

As Matthew Flaschen suggests, there is no need for VBA. This can be done with Excel formulae.

Suppose your data is in column A, starting at A1 and going down. Suppose cell B1 has the number x, which is the sample size you wish to use.

Then the formula you need should be:

=VAR(OFFSET(A1,0,0,B1,1))

The VAR part says calculate the variance of a sample of the population, ignoring non-numbers. (Variants to the function include VARP for the entire population, VARA to include non-numbers and VARPA for both).

The Offset part says to start at cell A1, move 0 columns across and zero columns down, and then select a range which is B1 columns tall and one column wide.