Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Michigan, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Averaging formula (2000 SR-1)

    Is there a way to average just the last 4 active cells in a row when the row might contain several blank cells in various locations? (i.e. in one row, you might have to go back 6 cells to get the last 4 active cells; in another row, all 4 of the last cells might be active; in yet another row, you have to go back 10 cells before you finally get 4 of them that are active)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts

    Re: Averaging formula (2000 SR-1)

    I think the easiest way is with a custom function:

    <pre>Function AvgLast(rng As Range, iNumber As Integer)
    Dim x As Long
    Dim lCount As Long
    Dim dSum As Double
    lCount = 0
    dSum = 0
    x = rng.Cells.Count
    Do While x > 0 And lCount < iNumber
    If Not IsEmpty(rng.Cells(x)) Then
    If IsNumeric(rng.Cells(x).Value) Then
    lCount = lCount + 1
    dSum = dSum + rng.Cells(x).Value
    End If
    End If
    x = x - 1
    Loop
    If lCount < iNumber Then
    AvgLast = CVErr(xlErrNA)
    Else
    AvgLast = dSum / lCount
    End If
    End Function</pre>


    Use it like:
    <pre>=AvgLast(A1:P1,4)</pre>


    to get the average of the 4 last values in the range A1-P1. If there are <4 values then it will give a #NA Error. If you want get the average of whatever is there (even if < the number you give) replace:
    <pre> If lCount < iNumber Then
    AvgLast = CVErr(xlErrNA)
    Else
    AvgLast = dSum / lCount
    End If</pre>


    with just:

    <pre> AvgLast = dSum / lCount</pre>


    Or you can replace the error with something else. cverr(xlErrNum) will give a #Num error (for example)

    I kept the formula "generic" having you fill in the number to "count". You could make it optional with the 4 as default, or even hard code it if desired, to eliminate the need to enter the 2nd parameter.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •