Multiple values within one cell

oayo

New Member
Joined
Apr 20, 2002
Messages
3
Is it possible to average a set of values entered into one cell? How?

Any information is appreciated.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Thanks for all the input. The last formula given by Aladin did the trick.

Thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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