Checking if the sum of a percentage is equal to one
Have a bit of a problem here.
Here's a sample sheet where I have reproduced my problem: http://pbsmith.iweb.bsu.edu/percentageSum.xls (you will need have macros enabled, as this is a VBA subroutine) I have 2 columns, where the individual percentages are added together. I have a "Grand total" which adds up the individual totals. I do a check against the grand total to make sure it's equal to one (using .Range() < 1) The problem is if there are 10 values of 10%, this check fails. If you replace one of the 10% values with two 5% values, the check succeeds (as it should, as long as it still adds up to 100%). I'm using Excel 2007. Any feedback on this problem would be greatly appreciated; I'm at a bit of a loss myself. |
Checking if the sum of a percentage is equal to one
One way to workaround that floating point problem
Sub test() Dim i As Long, a As Double Const FP As Double = 0.000000000000001 ' 1E-14 For i = 1 To 10 a = a + 0.1 Next Debug.Print a = 1 Debug.Print Abs(a - 1) < FP End Sub Increase the constant to whatever value is close enough to zero for your purposes., eg 1e-8 Regards, Peter T "PSmith" wrote in message ... Have a bit of a problem here. Here's a sample sheet where I have reproduced my problem: http://pbsmith.iweb.bsu.edu/percentageSum.xls (you will need have macros enabled, as this is a VBA subroutine) I have 2 columns, where the individual percentages are added together. I have a "Grand total" which adds up the individual totals. I do a check against the grand total to make sure it's equal to one (using .Range() < 1) The problem is if there are 10 values of 10%, this check fails. If you replace one of the 10% values with two 5% values, the check succeeds (as it should, as long as it still adds up to 100%). I'm using Excel 2007. Any feedback on this problem would be greatly appreciated; I'm at a bit of a loss myself. |
Checking if the sum of a percentage is equal to one
Excellent, thank you!
"Nigel" wrote: floating point arithmetic is the issue -- take a look here http://support.microsoft.com/kb/78113 -- Regards, Nigel "PSmith" wrote in message ... Have a bit of a problem here. Here's a sample sheet where I have reproduced my problem: http://pbsmith.iweb.bsu.edu/percentageSum.xls (you will need have macros enabled, as this is a VBA subroutine) I have 2 columns, where the individual percentages are added together. I have a "Grand total" which adds up the individual totals. I do a check against the grand total to make sure it's equal to one (using .Range() < 1) The problem is if there are 10 values of 10%, this check fails. If you replace one of the 10% values with two 5% values, the check succeeds (as it should, as long as it still adds up to 100%). I'm using Excel 2007. Any feedback on this problem would be greatly appreciated; I'm at a bit of a loss myself. |
All times are GMT +1. The time now is 03:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com