Average of a string containing multiple values
in [General]
|
Prev: Delete rows with duplicated values
Next: Hyperlink
From: Ron Rosenfeld on 23 Feb 2010 11:25 On Tue, 23 Feb 2010 08:47:47 -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 >> =============================== > >Good point... that should speed it up some, probably enough to become >quicker than Gary''s Student's UDF, I would guess. Mine does have one less function call: dSum/i vs zsum/(ubound(n)+1) --ron
From: Lori Miller on 23 Feb 2010 11:34 One more, since a normal formula was requested: =SUM(--(0&MID(A1,FIND("|",SUBSTITUTE(" "&A1&"|"," ","|",{1,2,3,4})), MMULT({1,-1},FIND("|",SUBSTITUTE(" "&A1&" |"," ","|",{1,2,3,4}+{1;0}))))) /(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)) This should be fairly efficient although I haven't tested it. Also fwiw, i think Biff's array formula may be able to be shortened using AVERAGE instead of SUM. "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: T. Valko on 23 Feb 2010 12:25 >This should be fairly efficient Yes, it's significantly faster than the version I suggested. 0.000290 vs. 0.000968 Another nice one from Lori! >Biff's array formula may be able to be shortened using AVERAGE Yeah, I should've realized that! -- Biff Microsoft Excel MVP "Lori Miller" <LoriMiller(a)discussions.microsoft.com> wrote in message news:37BE4209-E0FE-4DAF-953C-767FB61D8580(a)microsoft.com... > One more, since a normal formula was requested: > > =SUM(--(0&MID(A1,FIND("|",SUBSTITUTE(" "&A1&"|"," ","|",{1,2,3,4})), > MMULT({1,-1},FIND("|",SUBSTITUTE(" "&A1&" |"," ","|",{1,2,3,4}+{1;0}))))) > /(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)) > > This should be fairly efficient although I haven't tested it. Also fwiw, i > think Biff's array formula may be able to be shortened using AVERAGE > instead > of SUM. > > "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: Peter on 24 Feb 2010 10:36 Wow! Thanks to all you guys who have suggested answers. I went with Rons version, although I like Ricks one line version, and Biffs array does has the advantage of not producing the 'Enable/Disable' macros in sheet box. Thanks. Peter
From: T. Valko on 24 Feb 2010 12:19
This was a pretty interesting thread. Thanks for the feedback! -- Biff Microsoft Excel MVP "Peter" <Peter(a)discussions.microsoft.com> wrote in message news:F08EE59C-7E49-4913-BC81-D715DE1A684B(a)microsoft.com... > Wow! > > Thanks to all you guys who have suggested answers. > > I went with Rons version, although I like Ricks one line version, and > Biffs > array does has the advantage of not producing the 'Enable/Disable' macros > in > sheet box. > > Thanks. > > Peter > > > |