It doesn't add up - It's driving me crazy

[Guest]

I need someone to tell me why my macro can't add.

Try this:
Enter the following information in a blank workbook...
A1 = .2



--
Have a great day,
Francis Hayes (The Excel Addict)

http://www.TheExcelAddict.com
Helping Average Spreadsheet Users
Become Local Spreadsheet Experts

 

[Guest]

Sorry about that previous post. I was typing away and before I was finished
my post disappeared.

Here's the complete post...
-------------------------------------------
I need someone to tell me I'm not going crazy.

Please try this.

Enter the following data into a blank worksheet.
A1=.2
A2=.2
A3=.2
A4=.2
A5=.2

Notice that it adds up to 1 (i.e. 100%)

Copy this macro (below) into the workbook and run it.

Sub DoesntAddUp()
For Each c In Range("A1").CurrentRegion.Cells
amt = amt + c
Next c
If amt <> 1 Then
MsgBox "Sorry, your total is <> 1. Your total is = " & amt
Else
MsgBox "Congratulations. Your total is " & amt
End If
End Sub

You should get a message saying 'Congratulations. Your total is =1'

Now, change A1 to .3 and A5 to .1

Now run the macro again.

Did you get a message saying 'Sorry, your total is <> 1. Your total is = 1'?

Step through the macro and notice on the line 'If amt <> 1 Then' that the
variable amt = 1 but this still evaluates to TRUE.

Can someone explain to me what's happening? I've tried this on several
computers, each time with the same result.

Thanks for your help.
Francis

 

[Tom Ogilvy]

http://www.cpearson.com/excel/rounding.htm
Rounding And Precision In Excel

 

[JE McGimpsey]

Check out

http://cpearson.com/excel/rounding.htm

"Francis Hayes (The Excel Addict)"

 

[Bob Phillips]

What macro? What adding? Excel under-utilised?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Francis Hayes (The Excel Addict)"

 

[Trevor Shuttleworth]

Francis

maybe I'm missing something ... were you going to give a few more hints as
to what is happening ?

Regards

Trevor


"Francis Hayes (The Excel Addict)"

 

[Guest]

Check my followup post. The first one got send prematurely.

Francis

 

[Guest]

Check my followup post. The first one got send prematurely.

Francis

 

[Guest]

Rounding was the first thing I checked.

The point I am making is these are 5 one decimal values (not formula
results) where, according to my macro, .2+.2+.2+.2+.2 equals 1 but
..3+.2+.2+.2+.1 does not equal 1.

It seems that if the values are sorted in descending order with .1 as the
last value that's when I have the problem. If I sort these values in
ascending order it works fine.

Thanks,
Francis

 

[Tom Ogilvy]

At least have the courtesy to read the link. don't jump to conclusions. It
says rounding and precision in Excel. It explains how floating point
numbers are stored/represented and this is the root of your problem.


Here are a bunch more on the same or similar subjects:

http://support.microsoft.com/default.aspx?scid=kb;en-us;42980
(Complete) Tutorial to Understand IEEE Floating-Point Errors

http://support.microsoft.com/default.aspx?scid=kb;en-us;48606
XL: Comparison of Values Does Not Return Correct Result

http://support.microsoft.com/default.aspx?scid=kb;en-us;78113
XL: Floating-Point Arithmetic May Give Inaccurate Results

http://support.microsoft.com/default.aspx?scid=kb;en-us;165373
Rounding Errors In Visual Basic For Applications

http://support.microsoft.com/default.aspx?scid=kb;en-us;69333
HOWTO: Work Around Floating-Point Accuracy/Comparison Problems

--
Regards,
Tom Ogilvy



"Francis Hayes (The Excel Addict)"

 

[Fredrik Wahlgren]

It seems as if you only sum the values in cell A1.

For Each c In Range("A1").CurrentRegion.Cells

Shouldn't this line be rewritten as
For Each c In Range("A1:A5").CurrentRegion.Cells

/Fredrik


"Francis Hayes (The Excel Addict)"