Average of a string containing multiple values
in [General]
|
Prev: Delete rows with duplicated values
Next: Hyperlink
From: Rick Rothstein on 22 Feb 2010 15:54 See my response to Ron for a one-liner UDF. -- Rick (MVP - Excel) "Gary''s Student" <GarysStudent(a)discussions.microsoft.com> wrote in message news:D1004C6E-7D01-4660-A4DD-68DB7655B545(a)microsoft.com... > Ignore this post if a good non-VBA or array formula solution is posted. > Otherwise try the following UDF: > > Function sAver(r As Range) As Double > Dim v As String, zum As Double > v = r.Value > n = Split(v, " ") > For i = LBound(n) To UBound(n) > zum = zum + n(i) > Next > sAver = zum / (UBound(n) + 1) > End Function > -- > Gary''s Student - gsnu201001 > > > "Peter Noneley" wrote: > >> Hi, >> >> I am trying to calculate the average from a string that contains >> values. >> >> Example >> Cell A1 contains the string "10 20 30 40" >> I want a formula to calculate the Average of 25. >> >> The string can vary, such as >> "10 20 30" or "10 20" >> >> "1 2 3 4" or "1 2 3" or "1 2" >> >> The only constants are; >> - There will always be a space between values. >> - There will never be more than 4 values. >> >> I have tried using combinations FIND, SUBSTITUTE, MID and can get >> close to what I want, but the formula is very complicated and long and >> has to be split over six cells. >> >> It would be nice to have it in just one cell. >> >> I have also tried ="=average("&SUBSTITUTE(A1," ",",")&")" to create >> =AVERAGE(10,20,30,40) , but I then have no way of evaluating that a >> proper result. >> >> [ My Question ] >> Can the average be found using a formula in a single cell? >> I would prefer not to use VBA or Array formula. >> >> Thank you. >> >> Peter >> . >>
From: Ron Rosenfeld on 22 Feb 2010 21:10 On Mon, 22 Feb 2010 15:50:33 -0500, "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote: >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? --ron
From: Rick Rothstein on 22 Feb 2010 22:45 >>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: T. Valko on 22 Feb 2010 23:06 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: T. Valko on 22 Feb 2010 23:13
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) > > |