Multiple values within one cell

[oayo]

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

Any information is appreciated.

[Aladin Akyurek]

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?

[oayo]

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.

[Tom Schreiner]

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

[Tom Urtis]

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 ]

[Aladin Akyurek]

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

[oayo]

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

Thanks again.