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

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
.

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
.

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

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

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

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

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

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
.

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
.