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

Thread: Use one cell for several numerical values and then calc. ave

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Switzerland
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I would like to know if it possible to
    - enter several numerical data in one cell (like in an array: {35, 42, 37, 46})
    - and then calculate from another cell the average or standard deviation from these values

    Is there any function in Excel97 which do that.

    If not, has anybody an idea of a macro which I could use to calculate the average of a cell which contain the example above: {35, 42, 37, 46} .

    Would it be preferable to enter the array with "={35, 42, 37, 46}"

    Thanks for your help
    Emmanuel

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Cape Town,South Africa
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Emmanuel

    Why not just try the @average function


  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,060
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-08 00:14, Emmanuel wrote:
    Hi
    I would like to know if it possible to
    - enter several numerical data in one cell (like in an array: {35, 42, 37, 46})
    - and then calculate from another cell the average or standard deviation from these values

    Is there any function in Excel97 which do that.

    If not, has anybody an idea of a macro which I could use to calculate the average of a cell which contain the example above: {35, 42, 37, 46} .

    Would it be preferable to enter the array with "={35, 42, 37, 46}"

    Thanks for your help
    Emmanuel
    In A1 enter:

    {35, 42, 37, 46}

    In B1 enter: AVERAGE [ the built-in function of interest ]

    In C1 enter:

    =EVAL(B1&"("&A1&")")

    EVAL is available in Morefunc, an add-in downloadable from:

    http://longre.free.fr/english/index.html

    Aladin

  4. #4
    New Member
    Join Date
    May 2002
    Location
    Switzerland
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-08 00:28, Aladin Akyurek wrote:

    In A1 enter:

    {35, 42, 37, 46}

    In B1 enter: AVERAGE [ the built-in function of interest ]

    WHAT DO YOU MEAN WITH "[ the built-in function of interest ]". If I just put =average(A1) I get #value
    Emmanuel

    In C1 enter:

    =EVAL(B1&"("&A1&")")

    EVAL is available in Morefunc, an add-in downloadable from:

    http://longre.free.fr/english/index.html

    Aladin

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,060
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default


    WHAT DO YOU MEAN WITH "[ the built-in function of interest ]".


    AVERAGE, SUM, COUNT or such like are directly available from Excel itself, hence they are referred to as built-in. EVAL,on the other hand, is not.

    You wanted to compute AVERAGE and STDEV over the data that you want to enter in the following form:

    {35, 42, 37, 46}

    This requires a different approach than the standard way of computing. If you put 35, 42, 37, and 46 in consecutive cells, say, in A1:A4,

    =AVERAGE(A1:A4)

    would be the standard way of computing. The way the question is worded made me believe that you wanted something different than the standard way of doing things, whence my original reply.

    If I just put =average(A1) I get #value

    Of course, but this is not what I suggested that you do, see my original reply.

    Perhaps you should consider using standard mode of computing including putting every value of interest in a cell of its own.

    Aladin


    [ This Message was edited by: Aladin Akyurek on 2002-05-08 02:50 ]

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Cape Town,South Africa
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there a problem why you dont want to use the @average function excel is providing already Emanual?

    If you dont want to use it,please be more specific in what you want exactly?
    Do not take any offence to this message,but you are not clear enough friend.


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

    Default

    [quote]
    On 2002-05-08 01:54, Aladin Akyurek wrote:

    Of course, but this is not what I suggested that you do, see my original reply.


    **
    Thanks! It works now, I didn't understood well at first but now it's OK.
    Regards
    Emmanuel

  8. #8
    New Member
    Join Date
    May 2002
    Location
    Switzerland
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-08 02:45, gareth wrote:
    Is there a problem why you dont want to use the @average function excel is providing already Emanual?

    If you dont want to use it,please be more specific in what you want exactly?
    Do not take any offence to this message,but you are not clear enough friend.

    Gareth
    ... I'm back in the forum (not that I was offended)...
    Actually I don't understand what you mean with the function @average. How do you put it in a cell?
    Aladin gave me a solution but perhaps you've one without any additional function (EVAL in this case).
    What I want:
    - put several values in only one cell, e.g. 15;17;21;20;24...
    - calculate in another cell the average or std deviation
    If there is a way to do that with standard installation of Excel, may you gave me an example. Thanks!
    Regards
    Emmanuel

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
  •