From: Rick Rothstein on
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
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
>>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
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
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)
>
>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Delete rows with duplicated values
Next: Hyperlink