I have data on period of construction for dwellings in dissemination areas. I have transferred this data to my study areas and would like to determine the median period of construction for each study area. The only problem is that the columns' information is the number of dwellings and I do not want the median of these, but the title of the column to populate the row in a new field (or something else indicating it, preferably the period in text format, but it's not the end of the world if it's just a number indicating the period).

I am attaching a picture of the attribute table with the relevant fields highlighted. There is a MEDIAN field but the data I am using was created by someone else and poorly documented, so I am not certain as to whether the calculation was already conducted or not.

(The seven columns represent seven non-overlapping time periods ordered chronologically. The [Median] field appears to index the period at which total construction was half-complete; that is, it records the median time.)

Screen shot of table

  • 1
    Are you asking for the name (or index) of the field in which the median value occurs? (That clearly is not the current [Median] value.) Or are you perhaps asking for the name (or index) of the time period during which the first half of all construction was encountered? E.g., in row 1, total construction at the end of the periods went 45, 180, 485, 1495, 1995, 2130, 2130. The median of 2130/2 = 1065 occurred during the fourth period (and, sure enough, [Median]=4). That wouldn't normally be called a "median," but it looks consistent with the current data.
    – whuber
    Aug 16, 2011 at 19:04
  • @whuber, I'm just paranoid that it is incorrect, because the man who compiled this is in Vancouver and I can't ask him outright and he didn't document anything in the metadata.
    – Emily
    Aug 16, 2011 at 19:07
  • I still have no idea what "median column" means -- can someone rephrase the title and question?
    – blah238
    Aug 17, 2011 at 6:16
  • 1
    @blah The terminology is indeed correct, as explained at the beginning of my reply and further clarified in a comment to the reply by Nathanus. To respond to your request, I have added a paragraph to the question.
    – whuber
    Aug 17, 2011 at 12:54

2 Answers 2


Assuming the columns appear in time order, the first row (for example) indicates that total construction through each period went

0, 0+45 = 45, 45+135 = 180, 180+405 = 585, 585+1010 = 1595, ..., 2230+0 = 2230.

Construction was halfway through at 2230/2 = 1115. This occurred during period 4, because at the end of period 3 the total was 585, at the end of period 4 the total was 1595, and 585 <= 1115 < 1595.

This appears to be the result reported by the [Median] column, which gives the index of the period (starting at 1 on the left).

You can code this in your favorite language. The table is so small (100 rows), though, that a spreadsheet will be convenient, if only for checking what you do more formally in Python or whatever. Here's what it might look like:


The first three data rows have the same values as yours. The next two data rows (surrounded by blank lines) are chosen further down in your table. The last five data rows exercise the algorithm a bit.

(Note, as shown in the last two lines of the spreadsheet, how Excel chooses the later period whenever the middle falls exactly between two periods. This isn't necessarily the "right" answer, but it's a valid one.)

Here are the formulas in columns H:R:


You don't have to type them all. The only typing needed is:

  • =H2+A2 in I2. Drag this through O2. This computes the cumulative sums. It requires that columns A:G are in chronological order.

  • =O2/2 in P2. This finds half the total.

  • =Match(P2,H2:O2,1) in Q2. This indexes the column where construction was half complete.

  • =Offset($A$1:$G$1,0 0, Q2-1, 1, 1) in R2. This obtains the column heading corresponding to the index.

Then paste 0 in all of column H and drag I2:R2 down to as many rows as needed.

This effectively serves as pseudocode for the algorithm. The trickiest part will be the search to implement Excel's MATCH function. But that doesn't require any craft: it's not inefficient to search each array of cumulative sums sequentially (rather than with the preferred binary search algorithm) because these arrays are so short.

  • Thank you so much whuber! This is what I was looking for. :)
    – Emily
    Aug 16, 2011 at 19:41

If you want to return the median column, I would probably use a python cursor to iterate through rows. Use the fields to fill in a list and then return the center item (will be indexed at [3] for 7 items) after calling the sorted() function on the list you have created. I will write you a bit of code when I get back from lunch.

  • Hold off on coding until you're sure this is what's needed!
    – whuber
    Aug 16, 2011 at 19:07
  • Whether it's the year or the amount is only the difference between a dictionary and a list, I should think.
    – Nathanus
    Aug 16, 2011 at 19:08
  • I've been manually checking through a la @whuber's comment on my question and perhaps the column is correct, though it would be nice to know how to do this for future study-regions (which don't have these columns)
    – Emily
    Aug 16, 2011 at 19:28
  • I think it's a completely different calculation, @Nathanus. One views the data as an unordered set of counts {x1, x2, ..., x7} whereas the other views them as differences in a sequence (0, x1, x1+x2, x1+x2+x3, ..., x7) representing a cumulative distribution over time. The median of the counts will be a value exceeded by three of them and greater than or equal to the other three. The median of the cumulative distribution is a time, not a count, and is found using methods described in my reply.
    – whuber
    Aug 16, 2011 at 19:39
  • 1
    You're right: the distinction is not clear in the question. It only came out in the comments. I have inserted a short explanatory paragraph in the question.
    – whuber
    Aug 17, 2011 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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