12/4 M$ Excel qn: I have a column of 10000 numbers. Is there any easy way
to create another column of 1000 numbers where each number is the sum
(or average) of the corresponding 10 numbers in the first column? Thx.
\_ should be do-able with the Fill-Down menuu item.
\_ what exactly do you mean by corresponding 10 numbers?
\_ he means you have column A which has "1,2,3,4,5,6,7,8,9,10", and
he wants column B to have just one number (55) which is the sum of
the 10 in column A. There is probably an automated way to do that,
but I can't think of it off the top of my head. One thing you can
do is to use an IF statement in column B; it should be easy to
have the entry in column B be blank except for each 10th row.
Having the column B rows contiguous probably takes some scripting.
-tom
\_ can't you have column B have the SUM(A1:A10) then just fill
down? You'll have more info than you need, but you can work
with that.
\_ combining with tom's idea:
(IF((some rule to check if this is the 10th), SUM(A1:A10), "")
then fill down
\_ Yes, I can use the Subtotals function if I can let it be blank
except for each 10th row, but I need the numbers to be next to
one another.
\_ then I think you probably have to write a little macro to
put a formula in each cell. There might be a different way
but I've never figured out how to get "fill down" to be
more intelligent about how to increment the cell references.
-tom
\_ "On the Accuracy of Statistical Procedures in Microsoft EXCEL
97" B. D. McCullough, and Berry Wilson Abstract: The
reliability of statistical procedures in Excel are assessed in
three areas: estimation (both linear and nonlinear); random number
generation; and statistical distributions (e.g., for calculating
p-values). Excel's performance in all three areas is found to be
inadequate. Persons desiring to conduct statistical analyses of
data are advised not to use Excel.
(McCullough, BD, Computational Statistics and Data Analysis v31
(1999) 27-37.) [ reformatted - formatd ]
\_ gee that matters when you're trying to add.
\_ It's not that I like using Excel. I'm just required to use it
for this particular task. Otherwise I'd just write a C program
from scratch to calculate the stats and it'll still be much
faster than looking through the Excel Help. |
http://csua.com/feed/