From: T. Valko on
And to be fair, I should've tested Gary''s UDF...

This IS my final answer! <g>

Average calc time of 5 tests on a single cell.

A1 = 10 10 10 40

Gary''s UDF = 0.000306
Ron's UDF = 0.000310
Rick's UDF = 0.000362
Biff's array = 0.000968

--
Biff
Microsoft Excel MVP


"T. Valko" <biffinpitt(a)comcast.net> wrote in message
news:%23sLCE6DtKHA.5940(a)TK2MSFTNGP02.phx.gbl...
>I guess I should've also timed the array formula for a true comparison...
>
> Average calc time of 5 tests on a single cell.
>
> A1 = 10 10 10 40
>
> Ron's UDF = 0.000310
> Rick's UDF = 0.000362
> Biff's array = 0.000968
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "T. Valko" <biffinpitt(a)comcast.net> wrote in message
> news:%23GDoS2DtKHA.5036(a)TK2MSFTNGP02.phx.gbl...
>> Ron's is slightly faster.
>>
>> Average calc time of 5 tests on a single cell.
>>
>> A1 = 10 10 10 40
>>
>> Ron's = 0.000310
>> Rick's = 0.000362
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message
>> news:OgKRyqDtKHA.1440(a)TK2MSFTNGP06.phx.gbl...
>>>>>To Ron
>>>>>===========
>>>>>You know me and one-liners... see below for an even simpler (well, at
>>>>>least
>>>>>shorter) UDF. <g>
>>>>>
>>>>>To Peter
>>>>>===========
>>>>>Install this UDF using the same instructions Ron gave you for his
>>>>>UDF...
>>>>>
>>>>>Function AvgString(S As String) As Double
>>>>> AvgString = Evaluate("=AVERAGE(" & Replace(S, " ", ",") & ")")
>>>>>End Function
>>>>
>>>> But is it faster?
>>>
>>> I'm not sure, but my gut says no, it is not faster; however, for the
>>> size strings I think will be passed into it, I believe the time
>>> difference would be negligible.
>>>
>>> --
>>> Rick (MVP - Excel)
>>
>>
>
>


From: Rick Rothstein on
As I said, the speed difference with short strings between Ron's and my UDFs
(and Gary''s Student's as well) is basically negligible. I'm wondering if
Gary''s Student's UDF gets any faster with these slight tweaks...

Function sAver(r As Range) As Double
Dim zum As Double
n = Split(r.Value)
For i = 0 To UBound(n)
zum = zum + n(i)
Next
sAver = zum / (UBound(n) + 1)
End Function

--
Rick (MVP - Excel)


"T. Valko" <biffinpitt(a)comcast.net> wrote in message
news:eKRpuAEtKHA.1796(a)TK2MSFTNGP02.phx.gbl...
> And to be fair, I should've tested Gary''s UDF...
>
> This IS my final answer! <g>
>
> Average calc time of 5 tests on a single cell.
>
> A1 = 10 10 10 40
>
> Gary''s UDF = 0.000306
> Ron's UDF = 0.000310
> Rick's UDF = 0.000362
> Biff's array = 0.000968
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "T. Valko" <biffinpitt(a)comcast.net> wrote in message
> news:%23sLCE6DtKHA.5940(a)TK2MSFTNGP02.phx.gbl...
>>I guess I should've also timed the array formula for a true comparison...
>>
>> Average calc time of 5 tests on a single cell.
>>
>> A1 = 10 10 10 40
>>
>> Ron's UDF = 0.000310
>> Rick's UDF = 0.000362
>> Biff's array = 0.000968
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "T. Valko" <biffinpitt(a)comcast.net> wrote in message
>> news:%23GDoS2DtKHA.5036(a)TK2MSFTNGP02.phx.gbl...
>>> Ron's is slightly faster.
>>>
>>> Average calc time of 5 tests on a single cell.
>>>
>>> A1 = 10 10 10 40
>>>
>>> Ron's = 0.000310
>>> Rick's = 0.000362
>>>
>>> --
>>> Biff
>>> Microsoft Excel MVP
>>>
>>>
>>> "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message
>>> news:OgKRyqDtKHA.1440(a)TK2MSFTNGP06.phx.gbl...
>>>>>>To Ron
>>>>>>===========
>>>>>>You know me and one-liners... see below for an even simpler (well, at
>>>>>>least
>>>>>>shorter) UDF. <g>
>>>>>>
>>>>>>To Peter
>>>>>>===========
>>>>>>Install this UDF using the same instructions Ron gave you for his
>>>>>>UDF...
>>>>>>
>>>>>>Function AvgString(S As String) As Double
>>>>>> AvgString = Evaluate("=AVERAGE(" & Replace(S, " ", ",") & ")")
>>>>>>End Function
>>>>>
>>>>> But is it faster?
>>>>
>>>> I'm not sure, but my gut says no, it is not faster; however, for the
>>>> size strings I think will be passed into it, I believe the time
>>>> difference would be negligible.
>>>>
>>>> --
>>>> Rick (MVP - Excel)
>>>
>>>
>>
>>
>
>

From: Bernd P on
Hello Peter,

Array-enter
=AVERAGE(--sbSplit(A1," "))

You would need to install my addin Sulprobil to be able to use
sbSplit:
http://sulprobil.com/html/excel_addin.html

Regards,
Bernd
From: Ron Rosenfeld on
On Tue, 23 Feb 2010 00:08:08 -0500, "Rick Rothstein"
<rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote:

>As I said, the speed difference with short strings between Ron's and my UDFs
>(and Gary''s Student's as well) is basically negligible. I'm wondering if
>Gary''s Student's UDF gets any faster with these slight tweaks...
>
>Function sAver(r As Range) As Double
> Dim zum As Double
> n = Split(r.Value)
> For i = 0 To UBound(n)
> zum = zum + n(i)
> Next
> sAver = zum / (UBound(n) + 1)
>End Function

Well, if I were going to shorten mine, and make it equivalent to the others, I
could eliminate both the empty check cell as well as the TRIM function and
propose:

========================
Option Explicit
Function AvgString(s As String) As Double
Dim sTemp
Dim dSum As Double
Dim i As Long

sTemp = Split(s)

For i = 0 To UBound(sTemp)
dSum = dSum + sTemp(i)
Next i

AvgString = dSum / i

End Function
===============================

--ron
From: Rick Rothstein on
>>As I said, the speed difference with short strings between Ron's and my
>>UDFs
>>(and Gary''s Student's as well) is basically negligible. I'm wondering if
>>Gary''s Student's UDF gets any faster with these slight tweaks...
>>
>>Function sAver(r As Range) As Double
>> Dim zum As Double
>> n = Split(r.Value)
>> For i = 0 To UBound(n)
>> zum = zum + n(i)
>> Next
>> sAver = zum / (UBound(n) + 1)
>>End Function
>
> Well, if I were going to shorten mine, and make it equivalent to the
> others, I
> could eliminate both the empty check cell as well as the TRIM function and
> propose:
>
> ========================
> Option Explicit
> Function AvgString(s As String) As Double
> Dim sTemp
> Dim dSum As Double
> Dim i As Long
>
> sTemp = Split(s)
>
> For i = 0 To UBound(sTemp)
> dSum = dSum + sTemp(i)
> Next i
>
> AvgString = dSum / i
>
> End Function
> ===============================

Good point... that should speed it up some, probably enough to become
quicker than Gary''s Student's UDF, I would guess.

--
Rick (MVP - Excel)

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Delete rows with duplicated values
Next: Hyperlink