New Average Function 1

[kylua]

I've just read that you can create your functions and access them from Excel formulas so I created the below which I'm going to find useful:

It basically replaces:
=SUMIF(D8:H8,">0&quot/COUNTIF(D8:H8,">0&quot
with:
=ClnAvg(D8:H8)

with the benefit of you won't get a #DIV/0! if there is nothing to average.
[tt]
Function ClnAvg(Range)
Dim mycell As Object
counter = 0
thetotal = 0
For Each mycell In Range
If IsNumeric(mycell.Value) = True Then
If mycell.Value <> &quot;&quot; Then
thetotal = thetotal + mycell.Value
counter = counter + 1
End If
End If
Next mycell
If thetotal = 0 Then
ClnAvg = &quot;&quot;
Else
ClnAvg = thetotal / counter
End If

End Function
[/tt]
It can be altered to return 0 if there is nothing to average, or eliminate 0s from the input.

 

[VulcanJedi]

this should be an FYI

 

[VulcanJedi]

sorry i guess it is!

 

[strongm]

And the reason you wouldn't use the built-in AVERAGE function is?

 

[kylua]

Strongm

Cos if there is nothing for AVERAGE to average, it returns #DIV/0! This can be combatted with conditional formatting but still shows on printouts.

 

[ETID]

It's cool that you learned to build functions...however this function, I believe already exists in excel


------------------------------

The DAVERAGE function...

Averages the values in a column in a list or database that match conditions you specify.

For more information and examples, click .

Syntax

DAVERAGE(database,field,criteria)

Database is the range of cells that make up the list or database. A database is a list of related data in which rows of related information are records and columns of data are fields. The first row of the list contains labels for each column.

Field indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as &quot;Age&quot; or &quot;Yield,&quot; or as a number that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

Criteria is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying a condition for the column.

 

[kylua]

You're right, but mine's simpler to use.

The use for which I was intending was for monitoring activity on operator phone lines. There are many circumstances when there will be nothing to average. Such as the start of the month and Sundays.
Sticking the ClnAvg Function into a module and just using =ClnAvg(D830)is much simpler than bothering with criteria etc.
Altho that series of Dfeatures looks powerful.

 

[strongm]

My point is that the built-in Average function can be leveraged in your own custom function, eg:
[tt]
Public Function ClnAvg(myRange As Range) As Single
Dim Result As Single

Result = 0

On Error Resume Next
Result = Application.WorksheetFunction.Average(myRange)
On Error GoTo 0

ClnAvg = Result
End Function

 

[kylua]

Cool strongm

But have you not done exactly the same as me?

Different strokes, altho you have taught me how to use worksheetfunctions.

It's still a custom function. But yours doesn't update to &quot;&quot; if it finds an error. And an error of invalid input brings the whole thing crashing down.

 

[strongm]

My question was nothing to do with whether it was a custom function or not. My question was why you would want to write your own code for calculating an average when there is a built-in function to do just that. Would you, for example, write your own code to calculate the net present value of an investment when Excel already has a built-in NPV function?

>But yours doesn't update to &quot;&quot; if it finds an error

I specifically stated that the code I gave was just an example, to demonstrate how you might use the worksheet function within a user function. It was not intended as a drop-in replacement for your code. However, here's another, that produces output closer to your solution:
[tt]
Public Function ClnAvg(myRange As Range) As Variant
Dim Result As Variant

Result = &quot;&quot;

On Error Resume Next
Result = Application.WorksheetFunction.Average(myRange)
On Error GoTo 0

ClnAvg = Result
End Function
[/tt]
>an error of invalid input

Sorry, which invalid input is that?

 

[kylua]

Invalid input such as text which they both handle or an error code which mine handles.
The average function falls down on nothing to average and error codes as part of the range. I quite simply don't know how to make AVERAGE handle error codes or find a function that can, so I have written my own average function to do the job. It has to be said that if an AVERAGE function that handles empty ranges is used thru out the workbook, there shouldn't be any error codes to handle. Unless there's an error code generated from something else.

Anyway, it only took 10 mins to write so it's still a lot quicker than tracking down a suitable inbuilt function.

I was asked to write another one that just divided but didn't produce an error if it was dividing by zero. Easy enuff to do and only took another 10min or so, but is there an inbuilt function that handles that?