help me please:(i want to print averages using basic in excel..

databases Greece
  • 13 years ago

    hi!please help me!i have a total of 15000 values in excel in one column and i want every six values to print me their average in a differend cell each time.so that is 2500 averages..can you tell me a code for that and how to input it in excel?cause iam newbieSad [:(]

    nick

  • 13 years ago

    seems quite simple but your problem is getting cell ranges to jump each time by 6

    basic average is =AVERAGE(A1:A6) this will average the range A1 to A6

    but then you would want to fill down the next cell with =AVERAGE(A7:A12) for 2500 rows there may be an easy fill down method to do this but I am not aware of it however if you make a hidden column of numbers (in my example column B)  going 1,7,13,19 (add 6 each time) etc then use indirect in column C to obtain the correct ranges.

    =AVERAGE(INDIRECT("A"&B1):INDIRECT("A"&B2-1))

    INDIRECT turns a string of text into a cell reference so this above says =AVERAGE(A1:A6)

                   A               B                 C

    2 1 7
    4 7 19
    6 13 31
    8 19
    10 25
    12 31
    14 37
    16 43
    18 49
    20 55
    22 61
    24 67
    26 73
    28 79
    30 85
    32 91
    34 97
    36 103

  • 13 years ago
    sorry table didn't turn out properly  column c is 7, 19, 31 hope you understand
  • 13 years ago
    hi i have understand yor solution but i cant understand the use of column b?why you put numbers 1,7,13 e.tc?how can help me this column?thank you alot for your reply.. 
  • 13 years ago

    The indirect statement is a new one for me, so the way I would do it is:

    Dim xRows,myloop as Integer

    xRows = 15000

    For myloop = 1 To xRows
    If myloop Mod 6 = 0 Then
    Cells(myloop, 2).Formula = "=average(A" + CStr(myloop - 5) + ":A" + CStr(myloop) + ")"
    End If
    Next



Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“UNIX is basically a simple operating system, but you have to be a genius to understand the simplicity.” - Dennis Ritchie