Complex Averaging Question

[EXCELrulez]

Hello. I am hoping someone can help answer my question. I am working on a spreadsheet that will average 30 numbers, excluding zeros. These numbers are in a column and do include zeros. Below is the formula I have created, but it falls short.

=IF((SUM(F194:F208)=0)," N/A ", (AVERAGEIF((OFFSET(F208,0,0,(SUM(-30-(COUNTIF((OFFSET(F208,0,0,-30)),"=0")))))),">0")))

It will go back 30 rows, count the zeros, and then go back more rows for however many zeros it counted. It will then exclude the zeros from the average. The problem, however, there is no guaranteed it is averaging exactly 30 numbers. When it counts the zeros and extends the range, it picks up more zeros, resulting in an average less than 30 numbers. Would anyone have a suggestion on a formula that would guaranteed an average of 30 numbers? I am willing to look into VBA, if there are any suggestions.

Thanks for your help.

 

[joeu2004]

Let's say I want the answer in cell F201. The numbers to be averaged will be the last 30 non-zero numbers. Starting at F200 and ending at whatever cell happened to be at the end of the 30 non-zero numbers.

Okay, understood. I would put the following formula into F201, using the avgLastN function that I provided in response #5:

=avgLastN($F$1:INDEX(F:F,ROW()-1),30)

Alternatively, you could use the following implementation of avgLastN, which always assumes you want the last n non-zero numbers from the column above the cell containing the formula.

The problem with the following implementation is: it must be "volatile". That means the function is recalculated every time Excel recalculates any other cells in the workbook. Not a problem if you call avgLastN just a few times. But if you call avgLastN thousands of times, it could adversely affect performance of editing, opening and saving the Excel file as well as other recalculation cycles.

Function avgLastN(n As Long)
    Dim r As Long, c As Long, vCnt As Long
    Dim x As Variant
    ReDim v(1 To n) As Double
    Application.Volatile
    avgLastN = CVErr(xlErrNA)
    c = Application.Caller.Column
    For r = Application.Caller.Row - 1 To 1 Step -1
        x = Cells(r, c)
        If WorksheetFunction.IsNumber(x) Then
            If x <> 0 Then
                vCnt = vCnt + 1
                v(vCnt) = x
                If vCnt = n Then
                    avgLastN = WorksheetFunction.Average(v)
                    Exit Function
                End If
            End If
        End If
    Next
End Function

 

[EXCELrulez]

Thank you both so much for your help! I will examine both methods and see how they work. I will report back on my findings.

joeu2004 - Thank you for your help. I will try your method, but there could be scenarios on my sheet where it is used several hundred times. The workbook will be used for about a year. I'll give it a try and see how it will impact performance.

Aladin Akyurek - Thank you for your help too. I'm not familiar with some of the functions in your formula. If it isn't too much trouble, could you give me a brief explanation on how it works? Or point me into the direction of some documentation?


Thanks again for the assistance.

 

[joeu2004]

joeu2004 - Thank you for your help. I will try your method, but there could be scenarios on my sheet where it is used several hundred times. The workbook will be used for about a year. I'll give it a try and see how it will impact performance.

"Several hundred" should be okay. But again, why not use the original non-volatile avgLastN and the following expression?

avgLastN($F$1:INDEX(F:F,ROW()-1),30)