My excel sheet is populated for about 55,000 rows. There are two main columns – one has a name the other a number. There are about 2,400 names, with a couple of blank rows inserted after each name. I want to calculate a median for each name type. Is there a way to automate this process? Doing it manualy will be painful. Any help?
I don’t think there is built-in functionality for this. I would just sort the rows by name and write VBA code to scan down the list and print the median.
Found a vba code. Seems like it is working. Will have to audit. Thanks.
Does it have to be a median? If mean suffice then use sumif/countif with the name as criteria.
pivot tables! will be super easy to do this way. Just tested it, blank rows and all
Thanks all. The code I found works. In case some are interested in seeing the code (not sure whether it will be helpful without the excel file): “Sub MG18Jul07 Dim Rng As Range, Dn As Range Dim Fst As String, Lst As String, c As Long Set Rng = Range(Range(“O1”), Range(“O” & Rows.Count).End(xlUp).Offset(1)) For Each Dn In Rng If Dn <> “” Then c = c + 1 If c = 1 Then Fst = Dn.Address Lst = Dn.Address ElseIf Dn = “” Then With Dn .Value = Application.Median(Range(Fst & “:” & Lst)) .Interior.ColorIndex = 34 End With c = 0 End If Next Dn End Sub”
Sorry, I thought you could do this but you can’t actually in Excel 2007. I also tried it in Access. It’s possible but you’d have to use code. Here’s how you can do this in Excel: put your name data in col A and your numeric data in col B, then find the unique names in Col A (use a pivot table to do this) and spread them across row 1, starting in col C, horizontally. This is a header column that will be used in your median calculations. My Excel table looks like this, where there are 5 columns. Now, in cell C2, this will be the median of name = ‘a’, type this formula, but make sure to hit ctrl-shift-enter instead of enter: =MEDIAN(IF($A:$A=C1,IF($B:$B<>"",$B:$B,""),"")) this will compute the median of all non-blank values of the names in column 1 matching the value in C1, here, it will take the median of all names matching ‘a’ and will return 3.5 . drag across to E2 (or however far your names extend) a 1 a b c a 2 a 10 c 1 a 5 b 2 c 2 My result: a 1 a b c a 2 3.5 2 1.5 a 10 c 1 a 5 b 2 c 2