Need help with dynamic named ranges and medians based on specific criteria

Good afternoon everyone,
I've been lurking around for a while and have learned a good bit about excel and how to muddle through it, but I have run into a problem I cannot seem to solve.

I have attached a sample workbook with data and a brief description of what I need.

I am attempting to set up a spreadsheet where I can count matches in column B and count non matches in column B. If column B matches the criteria I need to obtain the median of column C, the median of column D and the median of column E as well as the median of Column D/Column C. I need it to be set up as dynamic named ranges as the number of rows will change frequently. I have removed the other columns from the data to make it easier to read and to remove any sensitive data. If it matters, there are typically 38 columns, but the ones included are the ones pertinent to the questions at hand. The number of rows will change frequently.

I have tried setting up the dynamic named ranges, but ran into particular problems with column B due to there being blank cells I think. So I thought I would reach out here to see if anyone has a simpler and more elegant solution to manually manipulating the data everytime I run this report.

Also, if at all possible, I need this to be functional in excel 97-2003. I have the current version, but the reports will be opened in an older version when I am done working on them.

Thank you tremendously

You can use the following formulae in the cells stated:

J5: =COUNTIF(B2:B154,"SLD")

J6: =COUNTIF(B2:B154,"<>SLD")

J7: =MEDIAN(IF(B2:B154="SLD",C2:C154)) ... NB - Array formula, see below

J8: =MEDIAN(IF(B2:B154="SLD",D2:D154)) ... NB - Array formula, see below

J9: =MEDIAN(IF(B2:B154="SLD",E2:E154)) ... NB - Array formula, see below

J10: =MEDIAN(IF(B2:B154="SLD",D2:D154/C2:C154)) ... NB - Array formula, see below

Note that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual Enter.

It is a 10-second job to apply named ranges to columns B to E, using the headings that are on row 1, so I suggest you do that instead of making the ranges dynamic.

Hope this helps.

Pete

I didn't realise that you already had your dynamic named ranges already set up using OFFSET - I prefer to use INDEX, which is not volatile. Here are the formulae using your named ranges:

J5: =COUNTIF(Status,"SLD")

J6: =COUNTIF(Status,"<>SLD")

J7: =MEDIAN(IF(Status="SLD",LP))

J8: =MEDIAN(IF(Status="SLD",SP))

J9: =MEDIAN(IF(Status="SLD",Days))

J10: =MEDIAN(IF(Status="SLD",SP/LP))

Note that the last 4 formulae are all array formulae, so you need to use CSE to commit them, as previously advised.

Hope this helps.

Pete

EDIT: Note also that these will be compatible with earlier versions of Excel.

Pete

Thanks for the assistance folks. Still having some trouble, but I'm working through it and hope this will help me to shave some time off my workload each day.

That's good to hear, and thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete