average every 3rd cell in a row

[jproffer]

Hi ladies and gents

I've searched some and found some solutions to averaging every nth cell in a row or column, but haven't been able to take the given solutions and modify them to what I'm doing.

Anyway, on to the point....

I'm trying to average every 3rd cell, in my case from H4:XFD4, starting with H4...in other words average H4, K4, N4, Q4, T4, W4, Z4....etc, to the end of the worksheet, and ignoring blanks (I already have the contributing formulas set up to be blank if that week doesn't yet have data). And hopefully (presumably, like every other formula in excel) I'd be able to drag/copy it down a few rows.

I found something like this, but I THINK (even though I can't decipher much of it beyond "average" and "if") this averages every 5th cell in a column, going down. I couldn't figure out how to change it to "every 3 in a row".

=AVERAGE(IF((MOD(ROW(D2:D2353)-ROW(D2)+1,5))=0,IF(D2:D2353<>"",D2:D2353)))



Thanks in advance for any help.

 

[Joe4]

Try this:

=AVERAGE(IF(MOD(COLUMN(H4:XFD4),3)=2,IF(H4:XFD4<>"",H4:XFD4)))

 

[jproffer]

Perfect. Guess I need to figure out how Mod and Column work so I actually understand.....but for the time being, just having the answer given to me is great.

Thanks so much Joe.

 

[Joe4]

Perfect. Guess I need to figure out how Mod and Column work so I actually understand.....but for the time being, just having the answer given to me is great.

You are welcome.

The concept is actually not too complicated.
The COLUMN function just returns the column number of the cell reference, i.e.
=COLUMN(H4)
would return 8.

The MOD function returns the remainder of of one number when divided by another, i.e.
=MOD(a,b)
returns the remainder when a is divided by b.

So, if we want to average cells H4, K4, N4, Q4, etc.
Those are column numbers 8, 11, 14, 17, etc.

Since it is every third column, we want to divide by 3 (our "b" number).
The numbers remainders of 8/3, 11/3, 14/3, 17/3, etc are all 2. So we only want to include values where the MOD function returns 2.

Does that help clarify things?

 

[jproffer]

Actually it does. If (<-- I understand that function ) ...If the remainder of x/3 = 2, then include that in the average range...otherwise don't. I had started to just type =average(click, click, click, click, click....etc) but that got old quick, this is much better, and now I know what I'm "reading" in that formula. Thanks for everything.

 

[Joe4]

Actually it does. If (<-- I understand that function ) ...If the remainder of x/3 = 2, then include that in the average range...otherwise don't. I had started to just type =average(click, click, click, click, click....etc) but that got old quick, this is much better, and now I know what I'm "reading" in that formula. Thanks for everything.

Yep, you got it!

 

[Mackeral]

Function Row_Sum(Row, Start_Col, Col_Increment)

    Dim Col, Last_Col As Integer
    
    With ActiveSheet
        Last_Col = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    
    Col = Start_Col
    Do
        Temp = ActiveSheet.Cells(Row, Col)
        If IsNumeric(Temp) Then
            Row_Sum = Row_Sum + Temp
        End If
        Col = Col + Col_Increment
    Loop Until Col > Last_Col
    
    
End Function

 

[jproffer]

Didn't even think about a UDF, but (without testing) that reads like it would add them up, then you'd have to divide by the result of another counter to count how many time Row_Sum was changed...looks good to me.

Good idea.

 

[liselbez]

Hi

How do you get the formula

You are welcome.

The concept is actually not too complicated.
The COLUMN function just returns the column number of the cell reference, i.e.
=COLUMN(H4)
would return 8.

The MOD function returns the remainder of of one number when divided by another, i.e.
=MOD(a,b)
returns the remainder when a is divided by b.

So, if we want to average cells H4, K4, N4, Q4, etc.
Those are column numbers 8, 11, 14, 17, etc.

Since it is every third column, we want to divide by 3 (our "b" number).
The numbers remainders of 8/3, 11/3, 14/3, 17/3, etc are all 2. So we only want to include values where the MOD function returns 2.

Does that help clarify things?

Hi, how do you get the formula to start counting at a specific cell nr. So if I need the formula to take the average of every 3rd cell starting at column I how do I incorporate that into the formula?

 

[liselbez]

Try this:

=AVERAGE(IF(MOD(COLUMN(H4:XFD4),3)=2,IF(H4:XFD4<>"",H4:XFD4)))

Hi, how do you get the formula to start counting at a specific cell nr. So if I need the formula to take the average of every 3rd cell starting at column I how do I incorporate that into the formula?