Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-12-2019, 01:59 PM
anne.kiss@ntlworld.com anne.kiss@ntlworld.com is offline excel macro returning the wrong value Windows 10 excel macro returning the wrong value Office 2013
Novice
excel macro returning the wrong value
 
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 excel macro returning the wrong value Windows 10 excel macro returning the wrong value Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
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 excel macro returning the wrong value Windows 7 64bit excel macro returning the wrong value Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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, your 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
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 03-13-2019, 12:44 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline excel macro returning the wrong value Windows 7 64bit excel macro returning the wrong value Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Perhaps you are the victim of Excel floating point arithmetic ?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #5  
Old 03-13-2019, 12:18 PM
anne.kiss@ntlworld.com anne.kiss@ntlworld.com is offline excel macro returning the wrong value Windows 10 excel macro returning the wrong value Office 2013
Novice
excel macro returning the wrong value
 
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 excel macro returning the wrong value Windows 7 64bit excel macro returning the wrong value Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
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
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 03-14-2019, 01:10 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline excel macro returning the wrong value Windows 7 64bit excel macro returning the wrong value Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
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...
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #8  
Old 03-14-2019, 05:53 AM
Marcia's Avatar
Marcia Marcia is offline excel macro returning the wrong value Windows 7 32bit excel macro returning the wrong value Office 2007
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
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
excel macro returning the wrong value 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 macro returning the wrong value Excel colums are wrong way around SteveL Excel 1 05-06-2015 02:09 AM
excel macro returning the wrong value 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:15 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft