Finding the Max Number in a Table based on Other Criteria

[cardan]

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

 

[Howard31]

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

 

[Bernd P]

Hello,

Search for MAXIF in this group.

Regards,
Bernd

 

[Howard31]

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

 

[cardan]

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

I think this is it Eirinberg! Before I solved it by creating a table
to the right and pulling out only the values related to my parameters
- per column. I then found the max with a max(R5:105) and the min with
an array {=MIN(IF(R5:R105>0,R5:R105,FALSE))} this gave me a table that
I did an Hlookup to.

I did look at the DMAX before but couldn't really figure it out until
your example. Thanks again!