bosco_yip said:
Hi Yogi,
My computer (Office XP) can give me the correct results, different Excel version problem ??
I am using your 2nd table :
1. database at cells A5:B25, with heading A5="DATE", B5="PCE", datas at A6:B25.
2. D4="DATE"
3. D5:D7={9/22/2005, 9/23/2005,9/24/2005}
Then, the formula in cells E5:E7 :
E5 =DMAX(INDEX($A$5:$A$25,MATCH(D5,$A$6:$A$25,0)):$B$25,2,D4:D5)
>> =DMAX($A$5:$B$25,2,D4:D5)
>> =76.468
E6 =DMAX(INDEX($A$5:$A$25,MATCH(D6,$A$6:$A$25,0)):$B$25,2,D5:D6)
>> =DMAX($A$10:$B$25,2,D5:D6)
>> =210.266
E7 =DMAX(INDEX($A$5:$A$25,MATCH(D7,$A$6:$A$25,0)):$B$25,2,D6:D7)
>> =DMAX($A$17:$B$25,2,D6:D7)
>> =557.276
Regards
Bosco Yip
Bosco,
Your results are correct (on Excel 2003), but they need an explanation for the label DATE in the criteria range is not available during the second and the third application of the DMAX formula and yet we get correct results.
Facts:
[1] Dates are in ascending order.
[2] The INDEX bit re-computes a subrange that the formula treates as the database range.
For the formula in E5, that is:
=DMAX(INDEX($A$5:$A$25,MATCH(D5,$A$6:$A$25,0)):$B$25,2,D4:D5)
the database is:
{"
DATE","
PCE";38617,58.744;38617,56.54;38617,52.948;38617,60.904;38617,76.468;...}
the criteria range is:
{"
DATE";38617}
For the formula in E6 when E5 is copied down, that is:
=DMAX(INDEX($A$5:$A$25,MATCH(D6,$A$6:$A$25,0)):$B$25,2,D5:D6)
the database is:
{
38617,
76.468;38618,89.654;38618,85.298;38618,76.876;38618,92.414;...}
the criteria range is:
{
38617;38618}
The way you invoked INDEX includes the last occurrence of 38617. Excel happily accepts
38617,
76.468 as labels of the database and
38617 as label in the Criteria range. This is strange because Excel would ordinarily complain about using numbers as field names in a database-like settings like lists.
Obviously, all this happens again when the formula is copied to E7:
=DMAX(INDEX($A$5:$A$25,MATCH(D7,$A$6:$A$25,0)):$B$25,2,D6:D7)
with as database: {
38618,
185.16;38619,49.462;38619,557.276;...}
and as criteria range: {
38618;38619}
All this can be replicated with a database in which the condition range consists of text values and this range is
sorted in ascending order.
| Book11 |
|---|
|
|---|
| A | B | C | D | E |
|---|
| 4 | | | | Name | Total |
|---|
| 5 | Name | Value | | | 229.136 |
|---|
| 6 | a | 58.744 | | b | 328.296 |
|---|
| 7 | a | 56.54 | | c | 1221.144 |
|---|
| 8 | a | 52.948 | | d | 2440.126 |
|---|
| 9 | a | 60.904 | | | |
|---|
| 10 | b | 76.468 | | | |
|---|
| 11 | b | 89.654 | | | |
|---|
| 12 | b | 85.298 | | | |
|---|
| 13 | b | 76.876 | | | |
|---|
| 14 | c | 92.414 | | | |
|---|
| 15 | c | 126.566 | | | |
|---|
| 16 | c | 210.266 | | | |
|---|
| 17 | c | 185.16 | | | |
|---|
| 18 | c | 49.462 | | | |
|---|
| 19 | c | 557.276 | | | |
|---|
| 20 | d | 329.208 | | | |
|---|
| 21 | d | 378.488 | | | |
|---|
| 22 | d | 409.196 | | | |
|---|
| 23 | d | 414.534 | | | |
|---|
| 24 | d | 426.646 | | | |
|---|
| 25 | d | 482.054 | | | |
|---|
|
|---|
E5, copied down:
=DSUM(INDEX($A$5:$A$25,MATCH(D5,$A$6:$A$25,0)):$B$25,2,D4:D5)
Database & Criteria range:
For the formula in E5:
{"
Name","
Value";"a",58.744;"a",56.54;"a",52.948;"a",60.904;...}
{"
Name";"a"}
For the formula in E6:
{"
a",
60.904;"b",76.468;"b",89.654;"b",85.298;"b",76.876;"c",92.414;...}
For the formula in E7:
{"
b",
76.876;"c",92.414;"c",126.566;"c",210.266;"c",185.16;"c",49.462;"c",557.276;...}
{"
b";"c"}
For the formula in E8:
{"
c",
557.276;"d",329.208;"d",378.488;"d",409.196;"d",414.534;...}
{"
c";"d"}
Recap:
When the database is sorted, one can calculate a subrange such that its first item can serve as a label along with a sorted criteria range: Thus, the same item appear as label both in database and in criteria range.
Does this indicate a bug in Excel? I don't think so although accepting numbers as labels here is inconsistent with other database-like settings, an issue MS should look into.
