Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts

    IF find Zero average the previous 5 values greater then 0

    Hello All,

    I need some formula works if in the column find zero then pick the average of last previous 5 values by excluding zeros. The file is attached with required results.

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Farrukh,

    Here's a user defined function (UDF) that will solve the problem.
    Code:
    Option Explicit
    
    Function dNoZeroAverage(Target As Range) As Double
    
      Dim dValTotal  As Double
      Dim iCntr      As Integer
      Dim lOffset    As Long
      
      iCntr = 0
      lOffset = -1
      
    '*** Exit the routine and return 0 if the value in Target
    '    = 0 or the row number is less than 5
    
      If Target.Value <> 0 Or Target.Row < 5 Then
        dNoZeroAverage = 0
        Exit Function
      End If
      
      Do
        If Target.Offset(lOffset, 0).Value <> 0 Then
          dValTotal = dValTotal + Target.Offset(lOffset, 0).Value
          iCntr = iCntr + 1
        End If
        
        lOffset = lOffset + -1
        
      Loop Until iCntr = 5
      
      dNoZeroAverage = dValTotal / 5
      
    End Function    'dNoZeroAverage
    Farrukh NoZeroAverage.xlsm
    Calling Formula: =dNoZeroAverage($B2)
    Place this formula in C2 (or which ever column you wish in row 2) of your workbook then fill down.
    After putting the code above in a module don't forget to save your file as an .xlsm or .xlsb file.

    HTH
    Attached Images Attached Images
    Last edited by RetiredGeek; 2013-12-09 at 12:22. Reason: Changed code & attachment per Steve's comments.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    farrukh (2013-12-12)

  4. #3
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Sir RetiredGeek,

    Thanks very much it is exaclty my need, just only if the values having decimal places the function does not count correct average. The sample is attached.

    Thanks Sir
    Attached Files Attached Files

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Change the line to:
    Dim dValTotal As Double

    And change all the
    lValTotal
    to
    dValTotal

    It was totaling up as a long which is still a integer quantity. With decimals, you need double precision...

    Steve

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve,

    DUH!

    I caught the fact that the function needed a Double return value (based on the sample answers) but completely missed the internal sum needing to be double since none of the sample data had decimals.

    Thanks for making the catch!

    P.S. Original post modified to reflect double both in code block and attachment.
    Last edited by RetiredGeek; 2013-12-09 at 12:23.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you had been taking your melange you would have seen the effect of the future use of decimals when you tested it. I think you need to make a Grocery run to Arrakis and restock your spice...

    Steve

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve,

    The problem is that we're not on any Guild Heighliner routes!
    Guild Heighliner.jpg
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #8
    2 Star Lounger
    Join Date
    Jun 2011
    Posts
    188
    Thanks
    62
    Thanked 0 Times in 0 Posts
    Thanks sir works perfectly

Posting Permissions

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