taking a number times a percentage, then dividing by that percentage does not equal zero, why?

[ChristopherBaber]

I'm taking fee1 + fee2 - refund - total paid / 35%
where total paid = 35% of fee1 + fee2 - refund

and I'm getting something like -2.8421709430404E-15 instead of 0

what is getting rounded to cause this issue?

the formula I'm using is

=(SUMIFS([@[Fee1]],[@[Fee1 Paid Date]],"<="&$U$1)+SUMIFS([@[Fee2]],[@[Fee2 Paid Date]],"<="&$U$1)-SUMIFS([@[Fees Refund]],[@[Refund Date]],"<="&$U$1)-[@[Total Paid ]]/35%)*5%

*$U$1= some date

I'm trying to calculate how much I might still owe someone. I pay a 35% commission on fee1 plus fee2. If I've already paid them their 35%, I think the formula above should show that I owe them 0, but it's rounding somewhere and showing I owe them -2.8421709430404E-15 instead of zero.

Where is the issue, and how do I fix it?

 

[rlv01]

It has to do with an issue Excel has with floating point precision. There is a lot of discussion about this if you search around with google. Here's one

Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

Discusses that floating-point arithmetic may give inaccurate results in Excel.

learn.microsoft.com

The usual method for problems like yours is to use rounding when appropriate.

 

[ChristopherBaber]

It has to do with an issue Excel has with floating point precision. There is a lot of discussion about this if you search around with google. Here's one

Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

Discusses that floating-point arithmetic may give inaccurate results in Excel.

learn.microsoft.com

The usual method for problems like yours is to use rounding when appropriate.

Rounding did work, thank you!