View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Howard31 Howard31 is offline
external usenet poster
 
Posts: 100
Default Finding the Max Number in a Table based on Other Criteria

Hi cardan,

I found the function yoy're looking for - DMAX

I've tried it on my spreadsheet and it works!

A B
1 Date Date
2 =19/03/2009 <=22/03/2009
3
4 Date Price
5 17/03/2009 997
6 18/03/2009 372
7 19/03/2009 555
8 20/03/2009 314
9 21/03/2009 16
10 22/03/2009 370
11 23/03/2009 234
12 24/03/2009 22
13
14 =MAX(B5:B12)= 997 =DMAX(A4:B12,"Price",A1:B2) = 555

--
A. Ch. Eirinberg


"cardan" wrote:

Hello All,

I am having an issue that I am not sure has an easy answer regarding
find the max number in a range of cells based on values within another
column within the same table. For Example;

I have a table that has 100 row of specific products (houses). Column
A has Days on Market, ranging between 1, 500. Column B is the Sales
Price of the houses.

I have created a summary table that finds how many units had a Days on
Market between 1 and 30 days, 31 and 100 days, and 101 to 500 days.

I also have formulas that finds the total and averages based on the
Days on Market ranges above. (It is a SUMPRODUCT with booleans.

Where I am stuck is writing a formula that will Find the Max (or Min)
price of only the product that is between the specific Days on Market
range. For example, I am looking for a formula that will search all
the houses that had 1-30 days on market and then return the max price
of only those houses.

Is this a simple formula or does it require a macro? If it is a macro,
does anyone have the coding for it? Any help would greatly be
appreciated! Thank you in advance