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

databases
• 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 newbie

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

### 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.”