Strange calculation

  • Thread starter Thread starter hans
  • Start date Start date
H

hans

I have a sheet counting scores on a question
One could score a question 1 to 5
Some put the scores in a spreadsheet but put them all in one cell
It looks like this
"555435"
I am ask to calulate the mean.
i know it is 27/6=4.5 but is there a formula who can do this
I got hundrds of thies scores.

Please help.

Greetings Hans
 
=AVERAGE(VALUE(MID("555435",ROW($A$1:$A$6),1)))

array ented (Ctrl-Shift-Enter)


Of cource, you can replace "555435" with a cell reference.

ROW(A$1:$A$6) does not reference any real data, it is just a trick to
get the sequence of numbers 1,2,...,6 (the number of scores).

Jerry
 
hans said:
I have a sheet counting scores on a question
One could score a question 1 to 5
Some put the scores in a spreadsheet but put them all in one cell
It looks like this
"555435"
I am ask to calulate the mean.
i know it is 27/6=4.5 but is there a formula who can do this
I got hundrds of thies scores.

Please help.

Greetings Hans
Click to expand...

I think the best way (particularly if not all your results have all the
scores in one cell) would be to separate the scores into six separate
columns, say B to G. You can do this with formulas such as
=MID(A1,1,1)*1
=MID(A1,2,1)*1
=MID(A1,3,1)*1
etc.
Then you can calculate the mean as normal using
=AVERAGE(B1:G1)
Once you have set up the 7 formulas for row 1, just copy down for your other
rows.
 
IF all the entries are in one cell, you could parse the
data into 6 columns. Select the cells, then use Data>
Text-to-Columns, Fixed Width, and add a line between each
digit.
 
I have a sheet counting scores on a question
One could score a question 1 to 5
Some put the scores in a spreadsheet but put them all in one cell
It looks like this
"555435"
I am ask to calulate the mean.
i know it is 27/6=4.5 but is there a formula who can do this
I got hundrds of thies scores.

Please help.

Greetings Hans
Click to expand...

With your value in A5, the array entered formula:

=AVERAGE(--MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1))

To array-enter, hold down <ctrl><shift> while hitting <enter>. XL will place
braces {...} around the formula.


--ron
 
Ron Rosenfeld said:
With your value in A5, the array entered formula:

=AVERAGE(--MID(A5,ROW(INDIRECT("1:"&LEN(A5))),1))

To array-enter, hold down <ctrl><shift> while hitting <enter>. XL will place
braces {...} around the formula.


--ron
Click to expand...

Thank you all.

Greetings Hans
Click to expand...
 
Back
Top