Average of a string containing multiple values
in [General]
|
Prev: Delete rows with duplicated values
Next: Hyperlink
From: T. Valko on 22 Feb 2010 23:25 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 23 Feb 2010 00:08 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 23 Feb 2010 06:39 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 23 Feb 2010 06:51 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 23 Feb 2010 08:47
>>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) |