Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 03-12-2019, 01:59 PM
anne.kiss@ntlworld.com anne.kiss@ntlworld.com is offline Windows 10 Office 2013
Novice
 
Join Date: Mar 2019
Posts: 2
anne.kiss@ntlworld.com is on a distinguished road
Default excel macro returning the wrong value


Hi Folks

Not sure if this has already been posted.

I have set up two variables as double in an excel macro and populated them with two values from cells in the workbook. Using Msgbox I print out the variables and get the expected values of 14.1 and 14.1. I use a further Msgbox to print out the difference between the two variables (variable 1 - variable 2) and I get -1.77635683940025E-15

I was expecting this to be zero.

I have tried recreating the excel file and making sure that the cells are all formatted as numbers with 1 decimal place.

I have tried setting the variables to zero immediately before inserting the values.

This is in a loop and other values are correct.

What am I doing wrong?
Reply With Quote
  #2  
Old 03-12-2019, 02:41 PM
Guessed's Avatar
Guessed Guessed is offline Windows 10 Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 1,153
Guessed has a spectacular aura aboutGuessed has a spectacular aura aboutGuessed has a spectacular aura about
Default

Are the two cells calculated values or were they typed in as 14.1 exactly?
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 03-12-2019, 03:09 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,390
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

anne.kiss: Please don't add unnecessary polls to threads. I hardly think you wanted people to vote on whether someone could help...

Furthermore, you thread title shows the question concerns Excel, not Word, yet you've posted in the Word VBA forum instead of the Excel programming forum. You'll have more chance of getting help if you post in the correct forum.

Poll stripped & thread moved to the correct forum.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #4  
Old 03-13-2019, 12:44 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,386
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Perhaps you are the victim of Excel floating point arithmetic ?
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #5  
Old 03-13-2019, 12:18 PM
anne.kiss@ntlworld.com anne.kiss@ntlworld.com is offline Windows 10 Office 2013
Novice
 
Join Date: Mar 2019
Posts: 2
anne.kiss@ntlworld.com is on a distinguished road
Default

Many thanks for these replies. The two values were calculated, not input manually.

Yes you are correct I added a poll by mistake - this was my first attempt at a post, so got it wrong.

When I went into this forum today my post was under excel programming, so maybe the website changes things for you?

Is there any way of getting round the floating point arithmetic? I only have one digit after the decimal point so I could split the number into two cells and do two calculations and then put the numbers back together again. This would be very messy, so if anyone knows how to make sure the floating point arithmetic is correct, please let me know.
Reply With Quote
  #6  
Old 03-13-2019, 01:55 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,390
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by anne.kiss@ntlworld.com View Post
When I went into this forum today my post was under excel programming, so maybe the website changes things for you?
No, the site doesn't do that of its own accord. Rather, as I said in my previous reply:
Quote:
Originally Posted by macropod View Post
Poll stripped & thread moved to the correct forum.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 03-14-2019, 01:10 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,386
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Quote:
Originally Posted by anne.kiss@ntlworld.com View Post
This would be very messy, so if anyone knows how to make sure the floating point arithmetic is correct, please let me know.
The article provided proposes solutions ( if you have read it entirely)

BTW using an email address as username is an open invitation for spammers...
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #8  
Old 03-14-2019, 05:53 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Location: Philippines
Posts: 182
Marcia is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
Thank you for this Pecoflyer. In the 2017 annual financial report that I prepared for our agency, the person in charge of consolidating all reports accused me of "force balancing" the accounts because when she manually entered all the data there was a .01 difference. I used the ROUND function in calculating the depreciation expenses but there's still the .01 problem. I will try the the Precision as Displayed option come year -end.
Reply With Quote
Reply

Tags
cell value, excel macro

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Table column width wrong - unless macro paused!? Kebap Word VBA 7 09-21-2018 03:14 PM
Returning text, date, time, location from one Excel sheet to another dave57 Excel 17 06-15-2017 10:45 AM
Excel colums are wrong way around SteveL Excel 1 05-06-2015 02:09 AM
Macro operates on wrong document window marceepoo Word VBA 1 12-20-2012 03:12 PM
need macro for quiz (?) repeat of wrong answers with shuffle braveshark PowerPoint 0 10-02-2012 06:10 AM


All times are GMT -7. The time now is 11:09 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft