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

Emmanuel

New Member
Joined
May 6, 2002
Messages
7
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
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
 
Upvote 0

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
 
Upvote 0
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. :biggrin:
 
Upvote 0
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
 
Upvote 0
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. :biggrin:
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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top