+ Reply to Thread
Results 1 to 5 of 5

Division in excel VBA producing wrong numbers

  1. #1
    Registered User
    Join Date
    07-22-2015
    Location
    202
    MS-Off Ver
    2013
    Posts
    2

    Division in excel VBA producing wrong numbers

    Hey guys, I've run into a really strange problem and I can't seem to figure out what I'm doing wrong.

    I'm trying to compute standard deviation in my script but when I go to do some pretty simple division it returns a number which is just pain wrong. I thought it might be because of the data types I was using but I've tried changing my variables to singles, doubles, integers, longs and nothing seems to get me what I need.

    Here's my code

    Please Login or Register  to view this content.
    The problem line is

    SumSq = (SumSq / ArraySize - 1)

    which returns 53.37044 instead of 56.1828. Any ideas why this might be happening?
    Last edited by SuaveDoesAmerica; 07-23-2015 at 01:49 PM.

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Division in excel VBA producing wrong numbers

    Suave please put code tags around your code.

    It's very simple. Edit your post, select all the code, choose the # on the tool bar and save the changes.

    It will end up looking like this.

    Please Login or Register  to view this content.
    Thanks.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  3. #3
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Division in excel VBA producing wrong numbers

    Quote Originally Posted by SuaveDoesAmerica View Post
    I can't seem to figure out what I'm doing wrong. I'm trying to compute standard deviation in my script but when I go to do some pretty simple division it returns a number which is just pain wrong. I thought it might be because of the data types I was using but I've tried changing my variables to singles, doubles, integers, longs and nothing seems to get me what I need.
    Without the data, it is difficult to check your results and offer a dispositive explanation.

    I suggest that you attach an example Excel file to a response here. You might need to click on Go Advanced to see the Attachments icon in the mini-toolbar.

    Quote Originally Posted by SuaveDoesAmerica View Post
    Please Login or Register  to view this content.
    All type Integer should be type Long. Especially IsCntr, which has the potential of incrementing to 253,623.

    All type Single should be type Double. This affects the precision of calculations.

    Quote Originally Posted by SuaveDoesAmerica View Post
    The problem line is
    SumSq = (SumSq / ArraySize - 1)
    which returns 53.37044 instead of 56.1828.
    How do you know that it should be 56.1828?

    Anyway, I wonder if you should write:

    SumSq = SumSq / (ArraySize - 1)

    That is the correct formula for the sample std dev, like Excel STDEV.

    By the way, I wonder if you can do simply:

    StdDev = WorksheetFunction.StDev(Issues)
    Last edited by joeu2004; 07-22-2015 at 01:52 PM. Reason: cosmetic

  4. #4
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Division in excel VBA producing wrong numbers

    joeu2004,

    Could you help us to get new posters to follow out standards?

    7b. Do not post a reply in a thread where a moderation request (e.g., title change, code tags) is still pending a response. - This applies to the original post and all participants within the thread.

  5. #5
    Registered User
    Join Date
    07-22-2015
    Location
    202
    MS-Off Ver
    2013
    Posts
    2

    Re: Division in excel VBA producing wrong numbers

    Sorry guys, thanks for your help.

    Anyway, I wonder if you should write:

    SumSq = SumSq / (ArraySize - 1)
    This solved the problem. Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Counta array is producing number that is wrong (not sure why)
    By jam320 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-11-2014, 05:00 PM
  2. [SOLVED]Formula producing wrong result
    By tobu56 in forum Excel Formulas & Functions
    Replies: 25
    Last Post: 10-26-2013, 08:59 PM
  3. vlookup producing wrong results
    By moley165 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-05-2013, 08:21 AM
  4. [SOLVED] Wrong division operation result in VBA
    By ilkaygumus in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-15-2012, 08:00 AM
  5. [SOLVED] Index and Match combination producing the wrong result
    By Authentik8 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-10-2012, 11:01 AM
  6. division gives wrong answer (division)
    By Brice in forum Excel General
    Replies: 5
    Last Post: 12-24-2010, 10:13 PM
  7. [SOLVED] How to stop automatic division of numbers in Excel
    By Justin in forum Excel General
    Replies: 2
    Last Post: 02-20-2005, 02:06 PM

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