How can I find the average of multiple columns in excel without selecting each individual row that I want averaged?

September 2, 2011 6:13 PM

How can I find the average of multiple columns in excel without selecting each individual row that I want averaged?

I am working on a homework assignment where I have 100 stocks listed in excel with their daily prices for a month beneath them in a row. I need to get the average and standard deviation for each stock over the course of that month. I know how to select each individual row of 30 days and get the average or sdev for it. How can I find these figures in excel without selecting each individual row? Is there a way to make a certain line find the average or standard deviation for all the numbers above it? If so, how can I do this? Thank you very much for your time and help. My statistics grade thanks you!

posted by gibbsjd77 to Computers & Internet (8 answers total)

Since it's a month you don't need to select an undefined number of rows above; you can simply do A1:Z1 (or however many trading days there are in a month, wherever your left-most stock is) and then fill your formula to the right. It's just like adding up a row of numbers and filling across columns, but transposed.
posted by michaelh at 6:23 PM on September 2, 2011

Response by poster: So what would the formula look like exactly?
posted by gibbsjd77 at 6:32 PM on September 2, 2011

Response by poster: I have stocks on B through CX and they have prices from from 2 to 32. How would I get all of the averages and standard deviations underneath them without pluggin in each row?
posted by gibbsjd77 at 6:34 PM on September 2, 2011

Best answer: in row B33, put "=average(B2:B32)"
in row B34, put "=stdev(B2:B32)"

Select rows B33 to CX34 (so you have those two formulas selected and are under all your other data), hit ctrl+r (cmd+r if you're on a mac, or find Fill Right in the toolbars)

Done.
posted by brainmouse at 6:36 PM on September 2, 2011

(for the record, you can also hit ctrl+d to fill down, and you can find the options for fill up and fill left in the menus, should you need them. You can also grab one cell with a formula you like, then grab it by the lower-right corner, which is kind of a bigger black box, and drag it to fill the formula in whatever direction)
posted by brainmouse at 6:38 PM on September 2, 2011 [1 favorite]

Response by poster: Thank you very much!!!!! I cannot adequately express how much help that was to me! Have a great night!
posted by gibbsjd77 at 6:43 PM on September 2, 2011

Response by poster: You're the bomb brainmouse! This assignment just got a million times easier:)
posted by gibbsjd77 at 6:54 PM on September 2, 2011 [1 favorite]

I usually use the click and drag method that brainmouse mentioned last - if you click the lower right corner then you can drag in whatever direction you want to fill the other rows. It adjusts automatically based on position - so if you have a formula using A3 and B14, and move one over it'll become B3 and C14, or if you move one down it'll become A4 and B15.

As a related tip, if there's one cell or part of the formula that you want to stay the same in all of your cells (say you're subtracting one value from each of the values in your row) you can use $ to 'lock' the row and column. e.g., A3-$B$14.
posted by Lady Li at 7:24 AM on September 3, 2011