DMAX taken to the max

G

Gus

Hi Dudes,

I badly need your great help in using DMAX and in using structured
references in DMAX’s criteria.

I have two tables, one called MeshTable, located on sheet Mesh, and another
called ExamTable, located on a sheet named Repository.

The table columns are as follows:

* MeshTable (starting in C3): Title | Title ID | Exam ID | Exam Title |
Earned? | Date

* ExamTable (starting in B3): ID | Item Title | Planned | Passed

I want to calculate the Passed cell in some rows of ExamTable. My goal is to
determine the latest of the dates a prerequisite of a title has been passed.
Example: For a title you have to pass two exams. I want to find the date of
the exam of the two you passed later than the other. If you took exam A in
2007 and exam B in 2004, I want to get 2007 as the result.

To support this, the value of Title ID and Exam ID in MeshTable comes from
the ID column of the ExamTable.

To make this calculation happen I found to use DMAX with this concept: In
MeshTable, I want to get the maximum value of the Date column where the Title
ID is equal to the value in the ID cell of ExamTable in the row hosting the
function (the “current rowâ€).

My DMAX function is this: =DMAX(MeshTable[#All];"Date";I$3:I$4)

The referenced region, I4:I5 contains the following:

Title ID
="=ExamTable[ID]"

Unfortunately, the result is 1900-01-00.

Before coming to the above criterion, I have tried some other variations,
like:

Title ID
="=ExamTable[[#This Row];[ID]"

and
Title ID
=â€MeshTable[[#All];[Title ID]] = ExamTable[[#This Row];[ID]]â€

But either these yielded the same default date or returned a #VALUE! error.
As you might have noticed, the criteria are on the right of ExamTable with
the headers aligned so that they are on the same row. If in this case I
remove the wrapper around the criterion ="=ExamTable[ID]" so the cell’s
content is =ExamTable[ID], this cell’s value will be the ID in the same row
of ExamTable. In this situation the formula correctly determines the date
(provided that I enter the ID in row 4 for which I want to calculate the
latest date).

Could you help me in writing the condition that would cause DMAX to evaluate
the criteria so that it tries to match the Title IDs with the ID in the same
row in which the formula resides?

All your help is greatly appreciated,
krank
 
D

Debra Dalgleish

Instead of DMAX, use an array entered MAX IF formula. For example, in
cell G3:

=MAX(IF(Mesh[Title ID]=ExamTable[[#This Row],[Item Title]],Mesh[Date]))

After you type the formula in the cell, press Ctrl+Shift+Enter, to array
enter the formula. Curly brackets will appear around the formula.

Hi Dudes,

I badly need your great help in using DMAX and in using structured
references in DMAX’s criteria.

I have two tables, one called MeshTable, located on sheet Mesh, and another
called ExamTable, located on a sheet named Repository.

The table columns are as follows:

* MeshTable (starting in C3): Title | Title ID | Exam ID | Exam Title |
Earned? | Date

* ExamTable (starting in B3): ID | Item Title | Planned | Passed

I want to calculate the Passed cell in some rows of ExamTable. My goal is to
determine the latest of the dates a prerequisite of a title has been passed.
Example: For a title you have to pass two exams. I want to find the date of
the exam of the two you passed later than the other. If you took exam A in
2007 and exam B in 2004, I want to get 2007 as the result.

To support this, the value of Title ID and Exam ID in MeshTable comes from
the ID column of the ExamTable.

To make this calculation happen I found to use DMAX with this concept: In
MeshTable, I want to get the maximum value of the Date column where the Title
ID is equal to the value in the ID cell of ExamTable in the row hosting the
function (the “current rowâ€).

My DMAX function is this: =DMAX(MeshTable[#All];"Date";I$3:I$4)

The referenced region, I4:I5 contains the following:

Title ID
="=ExamTable[ID]"

Unfortunately, the result is 1900-01-00.

Before coming to the above criterion, I have tried some other variations,
like:

Title ID
="=ExamTable[[#This Row];[ID]"

and
Title ID
=â€MeshTable[[#All];[Title ID]] = ExamTable[[#This Row];[ID]]â€

But either these yielded the same default date or returned a #VALUE! error.
As you might have noticed, the criteria are on the right of ExamTable with
the headers aligned so that they are on the same row. If in this case I
remove the wrapper around the criterion ="=ExamTable[ID]" so the cell’s
content is =ExamTable[ID], this cell’s value will be the ID in the same row
of ExamTable. In this situation the formula correctly determines the date
(provided that I enter the ID in row 4 for which I want to calculate the
latest date).

Could you help me in writing the condition that would cause DMAX to evaluate
the criteria so that it tries to match the Title IDs with the ID in the same
row in which the formula resides?

All your help is greatly appreciated,
krank
Click to expand...
 
G

Gus

Thank you very much, Debra, it works fine and as expected!

Gus

Debra Dalgleish said:
Instead of DMAX, use an array entered MAX IF formula. For example, in
cell G3:

=MAX(IF(Mesh[Title ID]=ExamTable[[#This Row],[Item Title]],Mesh[Date]))

After you type the formula in the cell, press Ctrl+Shift+Enter, to array
enter the formula. Curly brackets will appear around the formula.

Hi Dudes,

I badly need your great help in using DMAX and in using structured
references in DMAX’s criteria.

I have two tables, one called MeshTable, located on sheet Mesh, and another
called ExamTable, located on a sheet named Repository.

The table columns are as follows:

* MeshTable (starting in C3): Title | Title ID | Exam ID | Exam Title |
Earned? | Date

* ExamTable (starting in B3): ID | Item Title | Planned | Passed

I want to calculate the Passed cell in some rows of ExamTable. My goal is to
determine the latest of the dates a prerequisite of a title has been passed.
Example: For a title you have to pass two exams. I want to find the date of
the exam of the two you passed later than the other. If you took exam A in
2007 and exam B in 2004, I want to get 2007 as the result.

To support this, the value of Title ID and Exam ID in MeshTable comes from
the ID column of the ExamTable.

To make this calculation happen I found to use DMAX with this concept: In
MeshTable, I want to get the maximum value of the Date column where the Title
ID is equal to the value in the ID cell of ExamTable in the row hosting the
function (the “current rowâ€).

My DMAX function is this: =DMAX(MeshTable[#All];"Date";I$3:I$4)

The referenced region, I4:I5 contains the following:

Title ID
="=ExamTable[ID]"

Unfortunately, the result is 1900-01-00.

Before coming to the above criterion, I have tried some other variations,
like:

Title ID
="=ExamTable[[#This Row];[ID]"

and
Title ID
=â€MeshTable[[#All];[Title ID]] = ExamTable[[#This Row];[ID]]â€

But either these yielded the same default date or returned a #VALUE! error.
As you might have noticed, the criteria are on the right of ExamTable with
the headers aligned so that they are on the same row. If in this case I
remove the wrapper around the criterion ="=ExamTable[ID]" so the cell’s
content is =ExamTable[ID], this cell’s value will be the ID in the same row
of ExamTable. In this situation the formula correctly determines the date
(provided that I enter the ID in row 4 for which I want to calculate the
latest date).

Could you help me in writing the condition that would cause DMAX to evaluate
the criteria so that it tries to match the Title IDs with the ID in the same
row in which the formula resides?

All your help is greatly appreciated,
krank
Click to expand...
Click to expand...
 
D

Debra Dalgleish

You're welcome! Thanks for letting me know that it worked.
Thank you very much, Debra, it works fine and as expected!

Gus

:

Instead of DMAX, use an array entered MAX IF formula. For example, in
cell G3:

=MAX(IF(Mesh[Title ID]=ExamTable[[#This Row],[Item Title]],Mesh[Date]))

After you type the formula in the cell, press Ctrl+Shift+Enter, to array
enter the formula. Curly brackets will appear around the formula.

Hi Dudes,

I badly need your great help in using DMAX and in using structured
references in DMAX’s criteria.

I have two tables, one called MeshTable, located on sheet Mesh, and another
called ExamTable, located on a sheet named Repository.

The table columns are as follows:

* MeshTable (starting in C3): Title | Title ID | Exam ID | Exam Title |
Earned? | Date

* ExamTable (starting in B3): ID | Item Title | Planned | Passed

I want to calculate the Passed cell in some rows of ExamTable. My goal is to
determine the latest of the dates a prerequisite of a title has been passed.
Example: For a title you have to pass two exams. I want to find the date of
the exam of the two you passed later than the other. If you took exam A in
2007 and exam B in 2004, I want to get 2007 as the result.

To support this, the value of Title ID and Exam ID in MeshTable comes from
the ID column of the ExamTable.

To make this calculation happen I found to use DMAX with this concept: In
MeshTable, I want to get the maximum value of the Date column where the Title
ID is equal to the value in the ID cell of ExamTable in the row hosting the
function (the “current rowâ€).

My DMAX function is this: =DMAX(MeshTable[#All];"Date";I$3:I$4)

The referenced region, I4:I5 contains the following:

Title ID
="=ExamTable[ID]"

Unfortunately, the result is 1900-01-00.

Before coming to the above criterion, I have tried some other variations,
like:

Title ID
="=ExamTable[[#This Row];[ID]"

and
Title ID
=â€MeshTable[[#All];[Title ID]] = ExamTable[[#This Row];[ID]]â€

But either these yielded the same default date or returned a #VALUE! error.
As you might have noticed, the criteria are on the right of ExamTable with
the headers aligned so that they are on the same row. If in this case I
remove the wrapper around the criterion ="=ExamTable[ID]" so the cell’s
content is =ExamTable[ID], this cell’s value will be the ID in the same row
of ExamTable. In this situation the formula correctly determines the date
(provided that I enter the ID in row 4 for which I want to calculate the
latest date).

Could you help me in writing the condition that would cause DMAX to evaluate
the criteria so that it tries to match the Title IDs with the ID in the same
row in which the formula resides?

All your help is greatly appreciated,
krank
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word Template where user inputs <TITLE> and then that can be displayed in footers or headers [MAC] 0
Space at end of multi page list to accommodate new entries 1
Auto populate value from one task to another in MS PROJECT 0
Adding a big matrix in Word 2013 5
vlookup--2+ items in a cell to display 2+ results in another cell 0
vlookup with two data in a single cell 0
Pivot Table Calculated Field 0
Dsum and Dmax with structured references 1

Top