Work sheet function -average

[SUNIL]

Hi ,please help a newbie.
I need the average of cells A1:A5 in A6 , but some cells may be blank.
Please explain if you can. Thanks

Sunil

 

[Loomah]

Sunil
the Average function would be
=average(A1:A5) or in vba myAve = WorksheetFunction.Average(Range("A1:A5"))
to get the average ignoring blanks try
=sum(A1:A5)/counta(A1:A5)
or
myAve =
WorksheetFunction.Sum(Range("A1:A5"))/WorksheetFunction.CountA(Range("A1:A5"
))

 

[Norman Jones]

Hi Loomah,

to get the average ignoring blanks try
=sum(A1:A5)/counta(A1:A5)

Excel's Average function ignores blank cells by design.