Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need HELP!! IF OR formula driving me crazy!!!! | Excel Discussion (Misc queries) | |||
Driving me CRAZY~ please help | New Users to Excel | |||
Formula is driving me crazy????? | Excel Worksheet Functions | |||
VLOOKUP driving me crazy | Excel Discussion (Misc queries) | |||
Driving me crazy! | Excel Programming |