how to average a varying amount ou numbers

[cuewoz]

I want to include some code in a module to average a range of numbers
the problem is you dont know before hand how many numbers you wil
have.
When someone runs the spreadsheet they enter a number which will be th
amount of figures to be averaged out, but I don't know how to use th
active.formula with a Range(Cells(3, 4), Cells(11, 5)).Select
type of command.
Can anyone help?
I know there isn't much to go on but please ask me what else you migh
need to know.


Thanks

Warre

 

[Bernie Deitrick]

Warren,

Perhaps?

Activecell.Formula = "=AVERAGE(" & Range(Cells(3, 4), Cells(11, 5)).Address
& ")"

or to keep it in VBA:

myAverage = Application.Average(Range(Cells(3, 4), Cells(11, 5)))

HTH,
Bernie
MS Excel MVP

 

[cuewoz]

Hi Bernie
Thanks for that, but I cant get it working.
If it makes a difference I am running excel 2002.

The formula:
Activecell.Formula = "=AVERAGE(" & Range(Cells(3, 4), Cells(11
5)).Address
& ")"
Is like the one I am after and so I can insert some letter variables i
the cells positions.

You see the active cell where I want the average to be is offset fro
the list of numbers, and I get the amount of numbers in the list fro
the user, then when the user clicks a command button, it generates th
list of numbers. it does this ok and now I need to reference to the
but I can't use the range("AB17:AB19") format for example because
need to have the letters and numbers in describing the range variable
so I can easily change the area of range depending on the length o
list from the user, and because the length can be anything, I need t
be able to do this.

This is very frustrating as on the face of it appears like it should b
straightforward, but I can't see the solution.

Thank

 

[Bernie Deitrick]

cuewoz,

Hmmm..... Not sure _exactly_ what you mean, but let's say that the user
gives you the number of rows and columns (in the code below, the RowCount =
10 and ColCount = 2). For a range starting two rows below the activecell,
you could use something like this, that will average a 10 row by 2 column
range that starts at B5: It will result in the formula

=AVERAGE($B$5:$C$14)

in cell B3.

Sub Test()

Dim RowCount As Long
Dim ColCount As Integer

RowCount = 10
ColCount = 2

Range("B3").Select

ActiveCell.Formula = "=AVERAGE(" & Range(ActiveCell.Offset(2, 0), _
ActiveCell.Offset(RowCount + 1, ColCount - 1)).Address & ")"

End Sub

Take out the Range("B3").Select and get the rowcount and colcount from
inputboxes.....

HTH,
Bernie
MS Excel MVP