XL97: DFunctions Return Errors When Database Has Too Many Rows

Last reviewed: February 12, 1998
Article ID: Q162474

The information in this article applies to:

Microsoft Excel 97 for Windows

SYMPTOMS

In Microsoft Excel 97, when you type a formula that contains any of the worksheet database functions (also known as DFunctions), the formula returns either an error value or an incorrect value, instead of the correct value.

CAUSE

This problem occurs if the Database argument of a DFunction refers to a range that contains more than 32,768 rows. For example, if the defined names "Database" and "DataRange" refer to ranges that contain more than 32,768 rows, the following sample formulas do not work correctly:

   =DSUM(A1:B33000,"Field",Criteria)

   =DCOUNTA(A1:G65536,"Field2",Criteria2)

   =DAVERAGE(Database,"Field",Criteria)

   =DCOUNT(DataRange,"Salary",Criteria2)

NOTE: Because you cannot use more than 16,384 rows in a worksheet in earlier versions of Microsoft Excel, this problem does not occur in these versions.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel 97 for Windows. This problem was corrected in Microsoft Excel 97 for Windows, Service Release 1.

MORE INFORMATION

The following table lists the errors that are returned.

   DFunction     Error Value
   -------------------------
   DAVERAGE          #DIV/0!
   DCOUNT                  0
   DCOUNTA                 0
   DGET              #VALUE!
   DMAX                    0
   DMIN                    0
   DPRODUCT                0
   DSTDEV            #DIV/0!
   DSTDEVP           #DIV/0!
   DSUM                    0
   DVAR              #DIV/0!
   DVARP             #DIV/0!

In Microsoft Excel, you can use DFunctions to perform criteria- dependent calculations using data stored in a range in a worksheet. For example, you can use the DSUM function to sum values that match specific criteria in a column of records in a database.

All DFunctions use the following common syntax:

   =DFunction(Database,Field,Criteria)

REFERENCES

For more information about DFunctions in Microsoft Excel 97, click the Index tab in Microsoft Excel Help, type the following text:

   database functions

Double-click the selected text to view the "About database functions" topic.

Additional query words: 97 XL97 32768 32769 32,768 32,769
Keywords : xlformula kbualink97
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 12, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.