Set tiny amounts within tolerance to zero

Turn a leftover 0.004 into 0 when your tolerance is 0.01.

What you'll make

In Excel: 0.004 within 0.01 becomes 0.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1ABS(A8)The amount without its minus sign.0.004
2<=B8Checks it is no bigger than the tolerance.TRUE
3,0,Gives 0 when it is that small.0
4A8Otherwise keeps the amount as it is.Not used for row 8
5IF( … )Picks part 3 or part 4.0

An amount exactly equal to the tolerance also becomes 0. The amounts are made-up.

=IF(ABS(A8)<=B8,0,A8)

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own amounts. Paste amounts into column A and your approved tolerance into column B of the Template tab.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Use only a tolerance your team has approved. Too big a tolerance can hide real money.

Microsoft's help: ABS function