DMAX with multiple conditions in one column

[CTOM]

I am trying to transfer data one worksheet into another based on a set of conditions. I am using DMAX to select the largest value out of 4 or 5 values from one day and than match that to the same date in a column in my report worksheet. Based on my understanding of Database functions, I would have to have a column label repeated before every date in my report worksheet. Is this true?

I am also looking into index match scenarios, but I don't think I can get the max function to work. Any advice would be appreciated.

Thanks

DATE PCE
9/22/2005 58.744
9/22/2005 56.54
9/22/2005 52.948
9/22/2005 60.904
9/22/2005 76.468
9/23/2005 89.654
9/23/2005 85.298
9/23/2005 76.876
9/23/2005 92.414
9/23/2005 126.566
9/23/2005 210.266
9/23/2005 185.16
9/24/2005 49.462
9/24/2005 557.276
9/24/2005 329.208
9/24/2005 378.488
9/24/2005 409.196
9/24/2005 414.534
9/24/2005 426.646
9/24/2005 482.054

Report
9/24/05 MAX value of PCE (557)

 

[bosco_yip]

Hi Aladin,

Thank your for your excellent and detail explanation.

Regarding "accepting numbers as labels"

Syntax
DMAX(database,field,criteria)

It is noted that "field" can use either column label , or column position as per highlighted

But, the following 2 example is noted :

1] Example 1, the formula "field" used column position, of which can get correct results in the 1st, 2nd & 3rd

=DMAX(INDEX($A$5:$A$25,MATCH(D5,$A$6:$A$25,0)):$B$25,2,D4:D5)

2] Example 2, the formula "field" used column label , correct result got in the 1st, and #VALUE! in the 2nd & 3rd.

=DMAX(INDEX($A$5:$A$25,MATCH(D5,$A$6:$A$25,0)):$B$25,$B$5,D4:D5)

or

=DMAX(INDEX($A$5:$A$25,MATCH(D5,$A$6:$A$25,0)):$B$25,"PCE",D4:D5)

Therfore,

"accepting numbers as labels", the "field" must use column position.

Regards
Bosco Yip

 

[Aladin Akyurek]

Hi Aladin,

Thank your for your excellent and detail explanation.

Regarding "accepting numbers as labels"

Syntax
DMAX(database,field,criteria)

It is noted that "field" can use either column label , or column position as per highlighted

But, the following 2 example is noted :

1] Example 1, the formula "field" used column position, of which can get correct results in the 1st, 2nd & 3rd

=DMAX(INDEX($A$5:$A$25,MATCH(D5,$A$6:$A$25,0)):$B$25,2,D4:D5)

2] Example 2, the formula "field" used column label , correct result got in the 1st, and #VALUE! in the 2nd & 3rd.

=DMAX(INDEX($A$5:$A$25,MATCH(D5,$A$6:$A$25,0)):$B$25,$B$5,D4:D5)

or

=DMAX(INDEX($A$5:$A$25,MATCH(D5,$A$6:$A$25,0)):$B$25,"PCE",D4:D5)

Therfore,

"accepting numbers as labels", the "field" must use column position.

Regards
Bosco Yip

I think you missed the field issue. The fields of the database and the fields of the criteria range are what I was discussing.

 

[CTOM]

Yogi

Thanks for the data table tip. It is new to me and looks very useful. I was assuming you were solely using the database function and then let to my confusion along with script error I was getting on the page. The only problem I see if there are columns of data in between in the "date" column and "PCE" column, when using data table. Of course I hadn't told you about it, but I was still able to get this new data into my report worksheet, with relative ease.

Just to confirm - the database functions are limited by the fact that the range must include a column heading and adjacent criteria. So there is no way to use that feature for my problem, unless I inserted "date" in between each date value. Correct?

Thanks for all of your input - this is a great board.

 

[Yogi Anand]

Yogi

Thanks for the data table tip. It is new to me and looks very useful. I was assuming you were solely using the database function and then let to my confusion along with script error I was getting on the page. The only problem I see if there are columns of data in between in the "date" column and "PCE" column, when using data table. Of course I hadn't told you about it, but I was still able to get this new data into my report worksheet, with relative ease.

In my opinion, when applicable, it is a very useful feature. Confusion part ... no worries!

Just to confirm - the database functions are limited by the fact that the range must include a column heading and adjacent criteria. So there is no way to use that feature for my problem, unless I inserted "date" in between each date value. Correct?

No!(I mean not correct) from the best I can understand your question.
I wonder whether you have used the step by step sequence I laid out for you ... because if you had used that you would have noticed that the Date Label was used only once and the DataTable feature accomplished its feat by using multiple values as input into the criterion value input_cell. In my opinion this feature is applicable to your problem ... heck I thought that we were working on. Whether you want to use this feature for your problem or not, I can't say ... that is your choice.

Thanks for all of your input - this is a great board.

I agree this is a great board ... I learn some thing from it whenever I visit the Board!

 

[CTOM]

Yes the data base function coupled with the DataTable was the solution and worked very well. Thank you.

I meant database functions like DMAX, DSUM, Detc...have significant limits when other tools like DataTable are not used. I just wanted to make sure I fully understood the limitations of the Database functions.

Thanks again.

 

[Yogi Anand]

Yes the data base function coupled with the DataTable was the solution and worked very well. Thank you.

I meant database functions like DMAX, DSUM, Detc...have significant limits when other tools like DataTable are not used. I just wanted to make sure I fully understood the limitations of the Database functions.

Thanks again.

Hi CTOM:

The Dfunctions have their own place ... like the tools in an artisan's toolbox ... each tool has its own unique features. And yes you are right that without the use of DataTable each use of Dfunction will require its own set of criteria delineation.

I am glad that you were able to generate a solution to suit your needs ... Good Job ... now let us keep EXCELing!