Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to average a varying amount ou numbers
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 -- cuewo ----------------------------------------------------------------------- cuewoz's Profile: http://www.excelforum.com/member.php...fo&userid=3216 View this thread: http://www.excelforum.com/showthread.php?threadid=57090 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to average a varying amount ou numbers
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" wrote in message ... 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 will have. When someone runs the spreadsheet they enter a number which will be the amount of figures to be averaged out, but I don't know how to use the 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 might need to know. Thanks Warren -- cuewoz ------------------------------------------------------------------------ cuewoz's Profile: http://www.excelforum.com/member.php...o&userid=32161 View this thread: http://www.excelforum.com/showthread...hreadid=570900 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to average a varying amount ou numbers
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 -- cuewo ----------------------------------------------------------------------- cuewoz's Profile: http://www.excelforum.com/member.php...fo&userid=3216 View this thread: http://www.excelforum.com/showthread.php?threadid=57090 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to average a varying amount ou numbers
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 "cuewoz" wrote in message ... 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 in the cells positions. You see the active cell where I want the average to be is offset from the list of numbers, and I get the amount of numbers in the list from the user, then when the user clicks a command button, it generates the list of numbers. it does this ok and now I need to reference to them but I can't use the range("AB17:AB19") format for example because I need to have the letters and numbers in describing the range variables so I can easily change the area of range depending on the length of list from the user, and because the length can be anything, I need to be able to do this. This is very frustrating as on the face of it appears like it should be straightforward, but I can't see the solution. Thanks -- cuewoz ------------------------------------------------------------------------ cuewoz's Profile: http://www.excelforum.com/member.php...o&userid=32161 View this thread: http://www.excelforum.com/showthread...hreadid=570900 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trim varying decimals from column of numbers | Excel Worksheet Functions | |||
Calculate Amount with Varying Percentages per Thousands | Excel Discussion (Misc queries) | |||
transposing vertical data to horizontal with varying amount of data | Excel Discussion (Misc queries) | |||
How do I retain the format of varying numbers in Excel? | Excel Discussion (Misc queries) | |||
How do I see the amount in words (I will enter amount in numbers) | Excel Programming |