Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Multiple values within one cell

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is it possible to average a set of values entered into one cell? How?

    Any information is appreciated.


  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-21 01:11, oayo wrote:
    Is it possible to average a set of values entered into one cell? How?

    Any information is appreciated.

    Give an example of multiple values in a cell so we can see the way you enter them. And, Will you also point out the reason why you'd want to stuff them in a single cell?


  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Example: 5,3,3,,5

    Reason: Working on summarizing survey information. The values above would be five separate responses to the same survey subquestion. The blank value indicates that the person who returned the fourth survey did not answer this survey subquestion.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm assuming that a "no response" would not be figured in the average?
    However, if it is, what default/nuetral value is to placed?
    Tom

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    See if this array formula works for you.
    Control+Shift+Enter:
    =AVERAGE((MID(SUBSTITUTE(A1,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,",","")))),1))+0)

    Assumes cell A1 contains the survey values, with them being single digits as portrayed in your question.

    Any help?

    _________________
    Tom Urtis

    [ This Message was edited by: Tom Urtis on 2002-04-21 10:07 ]

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-21 09:22, oayo wrote:
    Example: 5,3,3,,5

    Reason: Working on summarizing survey information. The values above would be five separate responses to the same survey subquestion. The blank value indicates that the person who returned the fourth survey did not answer this survey subquestion.
    In B1 enter:

    =SUBSTITUTE(A1,",","")

    In C1 enter either:

    =IF(LEN(B1),SUMPRODUCT(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)+0)/(LEN(A1)-LEN(B1)+1),"")

    where no response is counted also as one.

    or

    =IF(LEN(B1),SUMPRODUCT(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)+0)/LEN(B1),"")

    where only actual responses are taken into account.

    I'd strongly suggest to record each response in a cell of its own in order to avoid computations by means of performance-sensitive hefty formulas as the above.

    Aladin

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for all the input. The last formula given by Aladin did the trick.

    Thanks again.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •