Caculate the average value of a range

[tulalit]

Hi guys. I need your help.
I need to calculate the average value of a range consisting of several cells. If among the cells in a range that contains blank, I want to write "BL" as a result, if not I will certainly write down the value of the real average of the numbers that exist within that range. thanks

 

[VoG]

Try

=IF(COUNTBLANK(A1:A5)>0,"BL",AVERAGE(A1:A5))

 

[tulalit]

Wow! Cool
How to write this in VBA?

 

[Scott Huish]

This VBA code works a little differently than that formula. It is going to check and see if there are the same amount of numbers in the range as there are cells in the range. I am using Range("A1:A5") in this example.

Sub test()
Dim c As Range, d As Long
Set c = Range("A1:B5")
d = Application.WorksheetFunction.Count(c)
If d = c.Cells.Count Then
    Range("A6") = Application.WorksheetFunction.Average(c)
Else
    Range("A6") = "BL"
End If
End Sub

 

[VoG]

You can just use the worksheet functions. Which range do you want to operate on and where should the result go? Or do you want a User Defined Function?

 

[tulalit]

Yes, that worked
What if the range is much more?
Thanks

 

[Scott Huish]

Just change the ranges in the code.

 

[tulalit]

Thanks a lot