Average of a string containing multiple values

[Peter Noneley]

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

 

[Ron Rosenfeld]

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

 

[Gary''s Student]

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

 

[T. Valko]

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!

 

[Rick Rothstein]

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)

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

 

[Rick Rothstein]

See my response to Ron for a one-liner UDF.

--
Rick (MVP - Excel)

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

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
.

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

To Ron

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.

 

[T. Valko]

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

 

[T. Valko]

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

 

[T. Valko]

And to be fair, I should've tested Gary''s UDF...

This IS my final answer! <g>

Average calc time of 5 tests on a single cell.

A1 = 10 10 10 40

Gary''s UDF = 0.000306
Ron's UDF = 0.000310
Rick's UDF = 0.000362
Biff's array = 0.000968

 

[Rick Rothstein]

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

--
Rick (MVP - Excel)

And to be fair, I should've tested Gary''s UDF...

This IS my final answer! <g>

Average calc time of 5 tests on a single cell.

A1 = 10 10 10 40

Gary''s UDF = 0.000306
Ron's UDF = 0.000310
Rick's UDF = 0.000362
Biff's array = 0.000968

 

[Bernd P]

Hello Peter,

Array-enter
=AVERAGE(--sbSplit(A1," "))

You would need to install my addin Sulprobil to be able to use
sbSplit:
http://sulprobil.com/html/excel_addin.html

Regards,
Bernd

 

[Ron Rosenfeld]

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
===============================

--ron

 

[Rick Rothstein]

As I said, the speed difference with short strings between Ron's and my

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.

 

[Ron Rosenfeld]

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

 

[Lori Miller]

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.

 

[T. Valko]

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

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.

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
.

 

[Peter]

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

 

[T. Valko]

This was a pretty interesting thread.

Thanks for the feedback!