Median conditional sum scenario

[omni72]

Hi Chandooans


I'm trying to come up with an efficient way to do the following:


1. Test a cell (we'll call it A3) for a specific time in text (i.e. 15:00)

2. Collect contents of a cell (call it H17) and below with A# cells that match the 15:00 time text

3. Output a median of that list


So, for example, if I'm wanting to find the median of the last three days at 15:00 it would need to need to collect H17, H31, and H45 then find the median of that list. For the 5-day median at 11:00, it would need to add H25, H39, H53, H67, and H81 then find the median.


Any suggestions?


If needed I can provide a spreadsheet with sample data.


Thanks, in advance, for your help.

 

[keymaster]

I am not sure I understand this correctly. Median is a middle value in a list (when sorted). So what do you mean by median of the sum?


If you meant average... then you can use below formula

(assuming time values are in G17:G100, numbers you want to average in H17:H100)


=averageif(G17:G100,A3,H17:H100)

 

[Luke M]

If you truly did want the median, then something like this array should work:

=MEDIAN(IF(MOD(A2:A5,1)=15/24,B2:B5))


Remember to confirm the array using Ctrl+Shift+Enter, not just Enter.

 

[omni72]

Chandoo: You are right, it is not a true 'sum'. It's more of a collected list than an actual sum. Poor wording on my part :D My apologies for the confusion. I edited my original post in hopes of adding clarity and reducing confusion.


Chandoo & Luke: I'll give your suggestions a try and report back.


Thanks for the ideas.


p.s. Here is a link to a sample workbook: http://dl.dropbox.com/u/1500039/CumulativeVolumeSample.xlsx


In the workbook, I would be looking to output the 3-day Median in column N, the 5-day in column O, and so forth. I'm evaluating column B starting at B17 for the timeframe. And using each time-matched entry from column H to build the list.