View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Alan Beban Alan Beban is offline
external usenet poster
 
Posts: 200
Default average of array element

If the functions in the frely downloadable file at
http://home.pacbell.net/beban are available to your workbook

Application.Sum(myArray)/(ArrayCount(myArray)-ArrayCountIf(myArray, 0))

Alan Beban

Geoff wrote:
Your suggestion works in a spreadsheet but not within an array the
array fills the empty cell with 0 not as empty. maybe I am
dimensioning my array incorrectly.

Thanks for the response.

On Jul 25, 8:22 pm, wrote:
I'm not sure if this will help but here is one way to delete all of
those zeros so then you can look at just the non-zero values
Dim myCell As Range
For Each myCell In Worksheets("Sheet1").Range("A1:BH100")
If myCell.Value = "0" Then
myCell.Clear
End If
Next myCell

you can change the clear to delete if you want the deleted. Hope it
helps.