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.

floating point arithmetic is the issue -- take a look here

http://support.microsoft.com/kb/78113


--

Regards,
Nigel
(E-Mail Removed)



"PSmith" <(E-Mail Removed)> wrote in message
news:ABEEBFAD-3D80-47FC-A198-(E-Mail Removed)...
> 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.

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" <(E-Mail Removed)> wrote in message
news:ABEEBFAD-3D80-47FC-A198-(E-Mail Removed)...
> 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.

Excellent, thank you!

"Nigel" wrote:

> floating point arithmetic is the issue -- take a look here
>
> http://support.microsoft.com/kb/78113
>
>
> --
>
> Regards,
> Nigel
> (E-Mail Removed)
>
>
>
> "PSmith" <(E-Mail Removed)> wrote in message
> news:ABEEBFAD-3D80-47FC-A198-(E-Mail Removed)...
> > 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.
>
>