up vote 0 down vote favorite
share [g+] share [fb]

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

link|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 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 at 15:07
Oh, so not a series but a series of formulas – Raystafarian Feb 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 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 at 15:16
feedback

3 Answers

up vote 2 down vote

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

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

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

=MEDIAN(INDIRECT("A"&(ROW()-1)*8+1&":A"&ROW()*8))
link|improve this answer
feedback
up vote 0 down vote

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.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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