Average of a string containing multiple values

  • Thread starter Thread starter Peter Noneley
  • Start date Start date
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
 
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
Click to expand...

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
 
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
 
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!
 
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
Click to expand...

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
Click to expand...
 
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
.
Click to expand...
Click to expand...
 
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
Click to expand...

But is it faster?
--ron
 
To Ron
But is it faster?
Click to expand...

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.
 
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
 
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
 
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
 
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
Click to expand...
 
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
Click to expand...

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
 
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
===============================
Click to expand...

Good point... that should speed it up some, probably enough to become
quicker than Gary''s Student's UDF, I would guess.
 
Good point... that should speed it up some, probably enough to become
quicker than Gary''s Student's UDF, I would guess.
Click to expand...

Mine does have one less function call:

dSum/i vs zsum/(ubound(n)+1)
--ron
 
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.
 
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
Click to expand...

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
.
Click to expand...
Click to expand...
 
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
 
Back
Top