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

[JustinChase]

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?

[#2 · Accepted solution]

@JustinChase 

The issue you are encountering is due to the nature of floating-point arithmetic in Excel. Computers represent decimal numbers approximately, which can lead to very small rounding errors in calculations. This is why you are seeing a result like -2.8421709430404E-15 instead of 0.

To handle this issue, you can use the ROUND function to round the result to a certain number of decimal places, effectively eliminating these tiny rounding errors.

Here is how you can adjust your formula:

1. Round the final result to a certain number of decimal places, such as 2 or more if needed:

=ROUND((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%, 2) 

2. Alternatively, round the intermediate results to ensure any potential rounding errors are minimized:

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

Explanation

• ROUND function: ROUND(number, num_digits) rounds a number to a specified number of digits.
• Rounding intermediate results: Ensures that each component of the formula is rounded before the final calculation to avoid cumulative rounding errors.

By using the ROUND function, you can eliminate the small differences caused by floating-point arithmetic, and ensure that your formula returns 0 as expected when the calculation is effectively zero.

Example with Rounding

Suppose you want to round to 2 decimal places:

=ROUND((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%, 2)

This will ensure that the result is rounded to 2 decimal places, preventing very small non-zero values that are due to floating-point precision issues. Adjust the num_digits parameter as needed based on the level of precision you require. The text, steps and functions were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#3]

@NikolinoDERounding solved the issue for me, thank you!