# Getting an interval of medians in Excel, from a single column of data?

I have a quite a large number of rows of of data in the 'A' column in Excel.

What I'd like to do is get the median in intervals of 8 answers (`=MEDIAN(A1:A8)`, then the median of the next 8 answers `=MEDIAN(A9:A16` and so on). How can I do this without manually changing the formula?

Advice would be greatly appreciated! :)

-
Do you want all the medians in the same cell? Where do you want the output? Which version of excel are you using? Are you familiar with macros? –  Raystafarian Feb 12 '12 at 15:05
Thanks for your reply. I'm looking to have the output as a series of medians in column B (ultimately there will be 1/8th as many medians in column B as there are cells in column A). I'm using Excel03 but have access to other versions. I'm not very familiar with macros, though I have used them before. –  user1205197 Feb 12 '12 at 15:07
Oh, so not a series but a series of formulas –  Raystafarian Feb 12 '12 at 15:09
You can just drag your formula down and delete the ones you don't need, it will change as you drag it down column b –  Raystafarian Feb 12 '12 at 15:14
Thanks, that is a clever idea I didn't think of, but wouldn't it still be quite a lot of effort to delete the irrelevant ones? –  user1205197 Feb 12 '12 at 15:16

Two possible approaches

1.Create the formula as a text value and then convert to a real formula, e.g. put this formula in B1 and copy down as far as you need

`="=MEDIAN(A"&ROWS(B\$1:B1)*8-7&":A"&ROWS(B\$1:B1)*8&")"`

Now convert to text by doing this

Copy column B then right click and use Paste Special > Values > OK

Now convert to a formula by doing this

Select column B and use Data > Text to columns > Finish

2.Use this formula in B1 copied down

`=MEDIAN(OFFSET(A\$1,ROWS(B\$1:B1)*8-8,0,8))`

The first method is a little more complex but you end up with the exact MEDIAN formulas you wanted. The second approach is simpler and gives the correct results but it's not obvious, looking at the formulas, what each one is doing

-
+1 from me as well –  Raystafarian Feb 12 '12 at 19:58
Yep, that works! Thanks! –  user1205197 Feb 13 '12 at 1:31

Below formula is the longest way of representation:-

=IF(ISERROR(MEDIAN(INDIRECT("A"&IF(ROW(A1)=1,ROW(A1),ROW(A1)+(7*(ROW(A1)-1)))):INDIRECT("A"&IF(ROW(A8)=8,ROW(A8),ROW(A8)+(7*(ROW(A1)-1)))))),"Not Applicable",MEDIAN(INDIRECT("A"&IF(ROW(A1)=1,ROW(A1),ROW(A1)+(7*(ROW(A1)-1)))):INDIRECT("A"&IF(ROW(A8)=8,ROW(A8),ROW(A8)+(7*(ROW(A1)-1))))))

But luckly barry got shortest representation.

-

You can use the following formula (in B1) and fill down:

``````=MEDIAN(INDIRECT("A"&(ROW()-1)*8+1&":A"&ROW()*8))
``````
-