+ Reply to Thread
Results 1 to 7 of 7

WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

  1. #1
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2007
    Posts
    75

    WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

    I used this loop:

    Please Login or Register  to view this content.
    to create an array named 'Y' of dates minus the minimum of the dates i.e. the earliest date. I then tried to take the sum of the dates using WorksheetFunction.Sum(Y) and it returned 0.

    Because I was confused, I used this loop to examine the contents of Y:

    Please Login or Register  to view this content.
    This showed me that the sum of the elements of 'Y' was greater than 0, as it contained numbers that were greater than 0 and no numbers less than 0.

    I then used this loop to create an array of these values:

    Please Login or Register  to view this content.
    and then executed WorksheetFunction.Sum(TestArray) and got the sum I expected, which was around 50,000. Has anyone got any idea as to why this might occur?

    Thanks in advance,

    Rowan
    Last edited by RowanB; 01-12-2012 at 05:56 AM.

  2. #2
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

    Hi,
    How did you declare Y?
    Good luck.

  3. #3
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2007
    Posts
    75

    Re: WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

    I declared Y in a separate module with:

    Public Y() As Date and

    then in the module of the procedure, I used ReDim Y(Count)

  4. #4
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

    That's the issue then. It would need to be Double for Sum to work on it.

  5. #5
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2007
    Posts
    75

    Re: WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

    Thanks a lot OnErrorGoTo0. That's excellent. What range of values can a Double take?

  6. #6
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

    -1.79769313486231E308 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308
    (lifted straight out of the Help file, so any inaccuracy is Microsoft's)

  7. #7
    Registered User
    Join Date
    11-13-2009
    Location
    Cumbria
    MS-Off Ver
    Excel 2007
    Posts
    75

    Re: WorksheetFunction.Sum returns 0 for array of elements whose sum is greater than 0

    Thanks a lot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1