Average of a string containing multiple values

P

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
 
R

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
 
G

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

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!
 
R

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)


Ron Rosenfeld said:
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
 
R

Rick Rothstein

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

--
Rick (MVP - Excel)


Gary''s Student said:
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 said:
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
.
 
R

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
 
R

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

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

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

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
 
R

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)


T. Valko said:
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
 
R

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
 
R

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.
 
R

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
 
L

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

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


Lori Miller said:
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 said:
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
.
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top