Home |
Search |
Today's Posts |
#1
|
|||
|
|||
database criteria
For the dmax example in excel help it shows the table below. It shows how to
find the max profit of apple and pear trees, or the max profit of apple trees. Because the criteria requires a column label ("Tree"), is there any way to find the max profit of just pear trees? I tried dmax(A4:E10,"Profit",A3), but this returned #VALUE! because the column label is not included in the criteria. Thanks! A B C D E F 1 Tree Height Age Yield Profit Height 2 Apple 10 <16 3 Pear 4 Tree Height Age Yield Profit 5 Apple 18 20 14 105.00 6 Pear 12 12 10 96.00 7 Cherry 13 14 9 105.00 8 Apple 14 15 10 75.00 9 Pear 9 8 8 76.80 10 Apple 8 9 6 45.00 |
#2
|
|||
|
|||
=sumproduct(max((a5:a10="Pear")*(e5:e10)))
"aspencer27" wrote in message ... For the dmax example in excel help it shows the table below. It shows how to find the max profit of apple and pear trees, or the max profit of apple trees. Because the criteria requires a column label ("Tree"), is there any way to find the max profit of just pear trees? I tried dmax(A4:E10,"Profit",A3), but this returned #VALUE! because the column label is not included in the criteria. Thanks! A B C D E F 1 Tree Height Age Yield Profit Height 2 Apple 10 <16 3 Pear 4 Tree Height Age Yield Profit 5 Apple 18 20 14 105.00 6 Pear 12 12 10 96.00 7 Cherry 13 14 9 105.00 8 Apple 14 15 10 75.00 9 Pear 9 8 8 76.80 10 Apple 8 9 6 45.00 |
#3
|
|||
|
|||
Array entered (meaning press Ctrl-Shift-Enter to commit the formula):
=MAX(--(A2:A7="Pear")*E2:E7) "aspencer27" wrote: For the dmax example in excel help it shows the table below. It shows how to find the max profit of apple and pear trees, or the max profit of apple trees. Because the criteria requires a column label ("Tree"), is there any way to find the max profit of just pear trees? I tried dmax(A4:E10,"Profit",A3), but this returned #VALUE! because the column label is not included in the criteria. Thanks! A B C D E F 1 Tree Height Age Yield Profit Height 2 Apple 10 <16 3 Pear 4 Tree Height Age Yield Profit 5 Apple 18 20 14 105.00 6 Pear 12 12 10 96.00 7 Cherry 13 14 9 105.00 8 Apple 14 15 10 75.00 9 Pear 9 8 8 76.80 10 Apple 8 9 6 45.00 |
#4
|
|||
|
|||
I should have pointed out that the column headings were in row 1 and the
formula assumes the data is in rows 2 thru 7 "aspencer27" wrote: For the dmax example in excel help it shows the table below. It shows how to find the max profit of apple and pear trees, or the max profit of apple trees. Because the criteria requires a column label ("Tree"), is there any way to find the max profit of just pear trees? I tried dmax(A4:E10,"Profit",A3), but this returned #VALUE! because the column label is not included in the criteria. Thanks! A B C D E F 1 Tree Height Age Yield Profit Height 2 Apple 10 <16 3 Pear 4 Tree Height Age Yield Profit 5 Apple 18 20 14 105.00 6 Pear 12 12 10 96.00 7 Cherry 13 14 9 105.00 8 Apple 14 15 10 75.00 9 Pear 9 8 8 76.80 10 Apple 8 9 6 45.00 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
database criteria | Excel Worksheet Functions | |||
Function that filters a list (Database) for criteria in a range a. | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Graphing Database Growth Rate | Charts and Charting in Excel | |||
Specify a null value in an Excel Database criteria range | Excel Worksheet Functions |