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

[Emmanuel]

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

[gareth]

Hi Emmanuel

Why not just try the @average function

[Aladin Akyurek]

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

[Emmanuel]

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

[Aladin Akyurek]

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 ]

[gareth]

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.

[Emmanuel]

[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

[Emmanuel]

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