excelvbaexcel-2007# Count average value of every 6th column in one row via VBA (Excel)

Right now I'm writing macro on VBA , I almost finished it, but right now i'm stuck on one issue. I can't understand how should I count average value of range that consists of every 6th column of my table. In excel this formula looks like "=AVG(I3, O3, U3...HE3)"

I tried to use macro recorder but it was just hard-coding average value by repeating the whole formula and every cell address. But my table updates everyday and everyday I add 6 new columns at the end of the table, left from last column and last column is column that stores average value of every 6th column of a row. I tried to do it through loop and I think that I'm pretty close to solving this issue, but I don't understand how to add looped values to a particular cell with formula.

Here is my code:

```
Dim i As Integer
i = 8
For i = 8 To rng.Columns.Count Step 6
rng.Cells(3, rng.Columns.Count) = Application.WorksheetFunction.Average(rng.Cells(3,i))
Next i
```

rng - is Range variable that stores my table.

With above code I managed to loop through all cells that I need to count avg value, but I don't understand how to get values of these cells to a particular cell that counts avg value. Could you please help me?

Solution

You can just sum the values then divide by the count of (columns-8)/6.

```
Dim i As Integer
Dim sum as long
i = 8
sum = 0
For i = 8 To rng.Columns.Count Step 6
sum = sum + rng.Cells(3,i).value
Next i
rng.Cells(3, rng.Columns.Count).offset(0,1).value = sum/((rng.columns.count-8)/6) ' I assumed you want the value in rng.columns.count +1, since but change it if you want.
```

EDIT, can't use rng.columns.count+1 I think since the range ends at the count. Changed it to offset.

Also note that we have no error checking in this code.

I would suggest something like `If Isnumber(rng.Cells(3,i).value) then`

before adding to the sum since a string value will break the code

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel