Hi cardan,

What I'll do is lets say dates are in column A, Prices are in Column B, in
cell C2 I'll put the following formula =IF(AND(B2=A$1,B2<=B$1),B2,""),
where A1 contains the min date and B1 the max date, you then copy the formula
down the cells, at the end you simply enter the formula =MAX(C2:C9), based on
the If formula before, cells in Column C will have identical values to column
B if they are in the right date range, otherwise they want have any values,
so the MAX function will only return MAX value out of the values that meet
the date range. Bellow is what I copied from my spreadsheet

A B
1 19/03/2009 22/03/2009

2 17/03/2009 997 =IF(AND(B2=A$1,B2<=B$1),B2,"")
3 18/03/2009 372 =IF(AND(B3=A$1,B3<=B$1),B3,"")
4 19/03/2009 555 555 =IF(AND(B4=A$1,B4<=B$1),B4,"")
5 20/03/2009 314 314 =IF(AND(B5=A$1,B5<=B$1),B5,"")
6 21/03/2009 16 16 =IF(AND(B6=A$1,B6<=B$1),B6,"")
7 22/03/2009 370 370 =IF(AND(B7=A$1,B7<=B$1),B7,"")
8 23/03/2009 234 =IF(AND(B8=A$1,B8<=B$1),B8,"")
9 24/03/2009 22 =IF(AND(B9=A$1,B9<=B$1),B9,"")
10 997 555

Hope this helps
--
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