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

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

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

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

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

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

--
Regards,
Tom Ogilvy

"Francis Hayes (The Excel Addict)"
wrote in message
...
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

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

Check out

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

In article ,
"Francis Hayes (The Excel Addict)"
wrote:

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.

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

What macro? What adding? Excel under-utilised?

--

HTH

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


"Francis Hayes (The Excel Addict)"
wrote in message
...
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

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

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)"
wrote in message
...
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

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

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

Francis


"Trevor Shuttleworth" wrote:

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)"
wrote in message
...
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

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

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

Francis

"Bob Phillips" wrote:

What macro? What adding? Excel under-utilised?

--

HTH

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


"Francis Hayes (The Excel Addict)"
wrote in message
...
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

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

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" wrote:

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

--
Regards,
Tom Ogilvy

"Francis Hayes (The Excel Addict)"
wrote in message
...
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

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

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...kb;en-us;42980
(Complete) Tutorial to Understand IEEE Floating-Point Errors

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

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

http://support.microsoft.com/default...b;en-us;165373
Rounding Errors In Visual Basic For Applications

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

--
Regards,
Tom Ogilvy



"Francis Hayes (The Excel Addict)"
wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy

"Francis Hayes (The Excel Addict)"
wrote in message
...
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

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

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)"
wrote in message
...
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