Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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! :)

share|improve this question
    
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
add comment

3 Answers

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

share|improve this answer
    
Barry thats great answer. +1 for your answer. –  Siva Charan Feb 12 '12 at 19:26
    
+1 from me as well –  Raystafarian Feb 12 '12 at 19:58
    
Yep, that works! Thanks! –  user1205197 Feb 13 '12 at 1:31
add comment

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.

share|improve this answer
add comment

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

=MEDIAN(INDIRECT("A"&(ROW()-1)*8+1&":A"&ROW()*8))
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.