Formula in brackets

[davidl222]

[TABLE="width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]0.25[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]0.56[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]0.07[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]0.07[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]0.03[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]0.02[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]xxxxx[/TD]
[/TR]
</tbody>[/TABLE]

If I put this formula into the cell marked xxxxx '=1-SUMIF(A1:A6,A8,B1:B6)' I get 0 as I expect

If I put this formula in '=(1-SUMIF(A1:A6,A8,B1:B6))' I get -2.22044604925031E-16 which is -0.00000000000000022 or something close

I need the brackets as this comes in the middle of a much longer formula.

Any ideas what I'm doing wrong

Thanks, I seem to have run out of talent.

Windown 7, Excel 2007

 

[Special-K99]

Probably Excel's internal calculation methods.

Try
=ROUND((1-SUMIF(A1:A6,A8,B1:B6)),2)

 

[Comfy]

Take a look at this: Floating-point arithmetic may give inaccurate results in Excel

If you a dealing with just 2 decimal places you could house your calculation within the round function.

 

[davidl222]

Thanks - that is a work around that works.