I want to find the average of multiple values stored in a single cell. For example the cell may contain 90, 80, 70, 60 and i want the second cell to return the average (75). The first cell will contain only two digit numbers, but the number of values may vary.
I appreciate this is probably a simple problem, but i'm an excel beginner and couldn't find a solution on google.
how many numbers are there (is it constant) and how are they seperated?
The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
<--- If a post helps hit the star
No the amount of numbers will vary, from roughly 2 to 10. At the moment they are separated by commas, but i could change this.
and you cant just sperad them over more cells?
i can do you a fomula to split evey "," then average, but if you cant do that then i think youll need a macro
Assuming first one is in A1:
=AVERAGE(--TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",100)),1+(100*(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)),100)))
confirmed with CTRL+SHIFT+ENTER not just ENTER
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
you can do it with a formula using evaluate as long as you know the position of the values cell. the defined name in this workbook uses the cell to the left
Josie
if at first you don't succeed try doing it the way your wife told you to
Thank you! Works beautifully.
Thank you twiggywales for quick response and trying to help as well.
Thanks as well JosephP.
you're welcome but mine's a mite awkward compared to NBVC's formula ;-)
insert /name /define
create a name called mysum
refers to =EVALUATE(SUBSTITUTE(!A1,",","+"))
then in b1 put
=mysum/(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1)
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hi,
I have the same problem. I tried the formula you gave and works but I only have one issue. In my case there is no space between the numbers in my cell or there is a text and/or "?"(please see exemple below)
8,756
8,749
8,751
18,146 ?
9,054 ?
I just write them in the cell and to separate I type alt+enter, without space or ",".
Is there any way to tell excel that my numbers are full stop ?
Thank you !
Administrative Note:Originally Posted by stefan.g
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #1 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi,
Thank you. As it was written that I have to check if there is already a topic with the same problem I thought I have to write in the same post.
I'll create another one.
have à nice day
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks