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 recomputes 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 databaselike 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 databaselike settings, an issue MS should look into.