Average of a string containing multiple values
in [General]
|
Prev: Delete rows with duplicated values
Next: Hyperlink
From: Peter Noneley on 22 Feb 2010 08:23 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 09:21 On Mon, 22 Feb 2010 05:23:17 -0800 (PST), Peter Noneley <xlfdic(a)hotmail.com> 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 Perhaps someone can come up with a non-array, non-VBA solution. But, if not, here is a simple UDF that will do what you request. To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this User Defined Function (UDF), enter a formula like =AvgString(A1) in some cell. ========================================== Option Explicit Function AvgString(s As String) As Double Dim sTemp Dim dSum As Double Dim i As Long sTemp = Split(WorksheetFunction.Trim(s)) If UBound(sTemp) = -1 Then Exit Function End If For i = 0 To UBound(sTemp) dSum = dSum + sTemp(i) Next i AvgString = dSum / i End Function ========================= --ron
From: Gary''s Student on 22 Feb 2010 09:22 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: T. Valko on 22 Feb 2010 12:38 >There will always be a space between values. Try this array formula** : All on one line. =SUM(IF(MID(" "&A1,COLUMN(1:1),1)=" ", --(0&MID(A1,COLUMN(1:1),FIND(" ",A1&" ", COLUMN(1:1))-COLUMN(1:1)))))/(LEN(A1)- LEN(SUBSTITUTE(A1," ",""))+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Note: this will only work with positive numbers! -- Biff Microsoft Excel MVP "Peter Noneley" <xlfdic(a)hotmail.com> wrote in message news:d5b238be-26c0-4cf3-b6e1-38b068d357fc(a)d27g2000yqf.googlegroups.com... > 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: Rick Rothstein on 22 Feb 2010 15:50
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 -- Rick (MVP - Excel) "Ron Rosenfeld" <ronrosenfeld(a)nospam.org> wrote in message news:8a45o5pcvo98ukq3lc3bns1f9gv6g9jf46(a)4ax.com... > On Mon, 22 Feb 2010 05:23:17 -0800 (PST), Peter Noneley > <xlfdic(a)hotmail.com> > 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 > > Perhaps someone can come up with a non-array, non-VBA solution. But, if > not, > here is a simple UDF that will do what you request. > > To enter this User Defined Function (UDF), <alt-F11> opens the Visual > Basic > Editor. > Ensure your project is highlighted in the Project Explorer window. > Then, from the top menu, select Insert/Module and > paste the code below into the window that opens. > > To use this User Defined Function (UDF), enter a formula like > =AvgString(A1) > in some cell. > > ========================================== > Option Explicit > Function AvgString(s As String) As Double > Dim sTemp > Dim dSum As Double > Dim i As Long > > sTemp = Split(WorksheetFunction.Trim(s)) > If UBound(sTemp) = -1 Then > Exit Function > End If > For i = 0 To UBound(sTemp) > dSum = dSum + sTemp(i) > Next i > > AvgString = dSum / i > > End Function > ========================= > --ron |