Average ?

  • Thread starter Thread starter Alien
  • Start date Start date
A

Alien

I have a string of numbers in the first 500 cells in column A.
I would like to get an average of the numbers in each cell, entered in
the cell to the right.
=average(a1) does not work.
an example of the cells look like this:
553762361
784
557317225842
4236282
45364
6546
78624
52
The desired result for the last cell would be 3.5.

(52) 5 +2 =7 /2=3.5
TIA
 
Use:

=AVERAGE(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

You must press ctrl/shift/enter after inserting the
formula to convert it to an array formula.

HTH
Jason
Atlanta, GA
 
Use:

=AVERAGE(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

You must press ctrl/shift/enter after inserting the
formula to convert it to an array formula.

HTH
Jason
Atlanta, GA
Click to expand...
 
Harlan Grove pointed out that if (a) the initial number is an integer greater than 0, and (b)
you want to continue to sum the digits until the result is a single digit, the formula is

=1+MOD(A1-1,9)

If that's what the OP wants, then the average of these digits should be

=(1+MOD(A1-1,9))/LEN(A1)

For his example 51 Mod 9 = 6, (1+6)/2 = 3.5.

Unfortunately the OP didn't tell us the result he wants for 784 and 6546, so the above may or
may not work.
 
Back
Top