Average of a string containing multiple values
 

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

Average of a string containing multiple values
 

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
.

Average of a string containing multiple values
 

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

Average of a string containing multiple values
 

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

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
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
.

Average of a string containing multiple values
 

On Mon, 22 Feb 2010 15:50:33 -0500, "Rick Rothstein"
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

Average of a string containing multiple values
 

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)

Average of a string containing multiple values
 

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" wrote in message
...
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)

Average of a string containing multiple values
 

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" wrote in message
...
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" wrote in message
...
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)

Average of a string containing multiple values
 

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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)

Average of a string containing multiple values
 

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" wrote in message
...
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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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)

Average of a string containing multiple values
 

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

Average of a string containing multiple values
 

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

Average of a string containing multiple values
 

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" wrote in message
...
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" 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
.

Average of a string containing multiple values
 

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

Average of a string containing multiple values
 

This was a pretty interesting thread.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Peter" wrote in message
...
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

Average of a string containing multiple values
 

On Wed, 24 Feb 2010 07:36:01 -0800, Peter
wrote:

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



Thanks for the feedback, Peter. You started an interesting discussion. Glad
to help.
--ron