# Division in excel VBA producing wrong numbers

1. ## 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?

2. ## Re: Division in excel VBA producing wrong numbers

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.

3. ## Re: Division in excel VBA producing wrong numbers

Originally Posted by SuaveDoesAmerica
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.

Originally Posted by SuaveDoesAmerica
``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.

Originally Posted by SuaveDoesAmerica
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)

4. ## 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. ## 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!

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

#### 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