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