# Thread: IF find Zero average the previous 5 values greater then 0

1. ## 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.

2. 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

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

farrukh (2013-12-12)

4. 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

5. 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. 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.

7. 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. Steve,

The problem is that we're not on any Guild Heighliner routes!
Guild Heighliner.jpg

9. 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
•