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 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), 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" 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. 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" wrote in message news:8a45o5pcvo98ukq3lc3bns1f9gv6g9jf46(a)4ax.com...> On Mon, 22 Feb 2010 05:23:17 -0800 (PST), 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 > > 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), 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  |  Next  |  Last Pages: 1 2 3 4 5 Prev: Delete rows with duplicated valuesNext: Hyperlink