Dmax Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the dmax function to get the max value from a data set and
everything works fine until similar records are found. For example, when it
returns the max value for "W69", it actually returns the max value for
"W69328". How can I find the value for only "W69"... is there a way to make
it return the max value only when an "Exact" match is found?

Thanks in advance!


Below is an extract from my data table:

All #/MH
W69 388
W69312 620
W69317 611
W69317 1017
W69318 437
W69318 719
W69323 1095
W69328 683
W69328 1363
W69370 334
W69372 318
 
Ndel40 said:
I am using the dmax function to get the max value from a data set and
everything works fine until similar records are found. For example,
when it returns the max value for "W69", it actually returns the max
value for "W69328". How can I find the value for only "W69"... is
there a way to make it return the max value only when an "Exact" match
is found?
Click to expand...
....

This is a consequence of a really STUPID design decision Microsoft made. For
the most part, Excel's DCOUNT, DSUM, DMAX, etc. functions are based on Lotus
123 Release 2 (which originated these functions). However, in its collective
wisdom, the Excel development team decided to depart from 123 criteria
syntax in this situation. They decided to treat such entries as "w69*".
Helpful of them, no?

[FWIW, I just tested this under 123R5, and entering only the criterion w69
matches cells containing exactly w69.]

Two ways around this. First just involves adding an equal sign AS TEXT to
the criteria entry. So enter a single quote first, then an equal sign, then
your text. So for w69, type

' = w 6 9

and press [Enter]. Second involves formula criteria. If your list range were
in A1:J1000 and the column headed All were column D, then your criteria
range would need to have nothing in the top row and the formula

=D2="w69"

in the cell below. This will restrict the record selection to matching the
entire value of each cell in column D. Both alternatives are
case-insensitive, so they'd match w69 and W69. If you want case-sensitive
matching, you have to use the formula criteria

=EXACT(D2,"w69")
 
Frank,

I does not seem to work.

What I would like to do is use the dmax function and put "W69" in the
criteria field and have it return 388... as shown in the table below.

In other words, is there some way to put "W69" in the criteria and make it
only return the corresponding value for "W69", not "W69XXX"?

This is my formula:

=DMAX(A3:B14,B3,G1:H2)

Thanks!
 
The AS TEXT option worked!!!

Thank You!!!

Harlan Grove said:
Ndel40 said:
I am using the dmax function to get the max value from a data set and
everything works fine until similar records are found. For example,
when it returns the max value for "W69", it actually returns the max
value for "W69328". How can I find the value for only "W69"... is
there a way to make it return the max value only when an "Exact" match
is found?
Click to expand...
....

This is a consequence of a really STUPID design decision Microsoft made. For
the most part, Excel's DCOUNT, DSUM, DMAX, etc. functions are based on Lotus
123 Release 2 (which originated these functions). However, in its collective
wisdom, the Excel development team decided to depart from 123 criteria
syntax in this situation. They decided to treat such entries as "w69*".
Helpful of them, no?

[FWIW, I just tested this under 123R5, and entering only the criterion w69
matches cells containing exactly w69.]

Two ways around this. First just involves adding an equal sign AS TEXT to
the criteria entry. So enter a single quote first, then an equal sign, then
your text. So for w69, type

' = w 6 9

and press [Enter]. Second involves formula criteria. If your list range were
in A1:J1000 and the column headed All were column D, then your criteria
range would need to have nothing in the top row and the formula

=D2="w69"

in the cell below. This will restrict the record selection to matching the
entire value of each cell in column D. Both alternatives are
case-insensitive, so they'd match w69 and W69. If you want case-sensitive
matching, you have to use the formula criteria

=EXACT(D2,"w69")
Click to expand...
 
Back
Top