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
|