Extracting records by date then calculating

[ptr_7]

Hi,
I have 2 columns of data, one is a date column and another a number column. Dates are often duplicated many times as there are multiple observations (numbers) per day. I need to extract all the values for each day, then find the median of these and return the result, so I have one single figure for each day in a new column. I think this involves a search by function such as VLOOKUP combined with the calculation to aggregate the results but don't seem to be getting anywhere.

Any ideas would be appreciated. Thanks
P

[NBVC]

Something like:

=MEDIAN(IF($C$5:$C$12=E5,$D$5:$D$12))

where C5:D12 contain your table and E5 contains the date to find the median for.

Adjust the ranges and reference to suit and then confirm the formula with CTRL+SHIFT+ENTER not just ENTER...you will see { } brackets appear around the formula. You can then copy it down.

[TIPPYS]

have you considered using a pivot table?

[ptr_7]

No I haven't tried pivot reports, but I'll look into that thanks.
P

[TIPPYS]

Here is an example that might give you something similar to what you want. there is a macro that is set so when the observation column changes it updates the pivot table.