Combination

Round-sum journal entries with FILTER, MOD, ROUND and ABS

Flag ledger lines whose amount is an exact multiple of 1,000 (and above a minimum): ABS removes the sign, ROUND clears floating-point noise, MOD tests the multiple and FILTER lists the lines.

Functions used:FILTERMODROUNDABSExcel 2021 or later

Difficulty rating

Worked example

Ledger lines with round amounts

Scenario

For journal entry testing at Veldhuis Installatie BV you look for lines with a round amount: exact multiples of 1,0001.000 are a fraud-risk indicator, especially in manual entries.

You type the multiple in G1 and a minimum amount in G2, so small round amounts such as 3,0003.000 do not clutter the list. Credits are stored as negative amounts. FILTER needs Microsoft 365 or Excel 2021 or later.

The data

ABCDEFGHIJK
1JournalEntry no.DateDescriptionAmountMultiple1,0001.000
2MEM25-080131 Mar 2025Holiday pay accrual€18,400.00€ 18.400,00Minimum amount5,0005.000
3INK25-04122 Apr 2025Rent invoice€52,300.00€ 52.300,00
4MEM25-083330 Jun 2025Revenue correction-€96,000.00€ -96.000,00MEM=FILTER(A2:E9, (MOD(ROUND(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2), "No round-sum lines")=FILTER(A2:E9; (MOD(ROUND(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2); "No round-sum lines")25-083330 Jun 2025Revenue correction-€96,000.00€ -96.000,00
5BNK25-059015 Jul 2025Customer receipt€25,000.00€ 25.000,00BNK25-059015 Jul 2025Customer receipt€25,000.00€ 25.000,00
6MEM25-084515 Sep 2025Reclass payables€3,000.00€ 3.000,00MEM25-085228 Nov 2025Rent reclass€15,000.00€ 15.000,00
7MEM25-087131 Dec 2025Bonus accrual€41,200.00€ 41.200,00
8MEM25-088031 Dec 2025VAT correction€12,500.50€ 12.500,50
9MEM25-085228 Nov 2025Rent reclass€15,000.00€ 15.000,00
10
Formula cell (F4)InputSpilled result

Formula in F4

=FILTER(A2:E9, (MOD(ROUND(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2), "No round-sum lines")
=FILTER(A2:E9; (MOD(ROUND(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2); "No round-sum lines")

Result: MEM | 25-0833 | 30 Jun 2025 | Revenue correction | -€96,000.00 / BNK | 25-0590 | 15 Jul 2025 | Customer receipt | €25,000.00 / MEM | 25-0852 | 28 Nov 2025 | Rent reclass | €15,000.00MEM | 25-0833 | 30 Jun 2025 | Revenue correction | € -96.000,00 / BNK | 25-0590 | 15 Jul 2025 | Customer receipt | € 25.000,00 / MEM | 25-0852 | 28 Nov 2025 | Rent reclass | € 15.000,00

How the pieces fit together

Watch out: Always round amounts that contain cents before testing them with MOD. A computed amount can carry a tiny floating-point error, and a round amount is then missed.

The formula taken apart, from the inside out. Each part can be typed into its own cell to see what it returns.

  1. =ROUND(ABS(E2:E9), 2)
    =ROUND(ABS(E2:E9); 2)

    ABS strips the sign and ROUND rounds the amount to two decimals. The eight amounts become 18,400, 52,300, 96,000, 25,000, 3,000, 41,200, 12,500.50 and 15,000. The -96,000 credit is now positive.

  2. =MOD(ROUND(ABS(E2:E9), 2), G1)
    =MOD(ROUND(ABS(E2:E9); 2); G1)

    MOD returns what is left after dividing by the multiple in G1 (1,000). The eight remainders are 400, 300, 0, 0, 0, 200, 500.50 and 0. A remainder of 0 means an exact multiple.

  3. =(MOD(ROUND(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2)
    =(MOD(ROUND(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2)

    The first test is TRUE for lines 3, 4, 5 and 8 (remainder 0). The second test, amount of at least the minimum in G2 (5,000), is FALSE only for line 5 (3,000). Multiplying gives 0, 0, 1, 1, 0, 0, 0, 1: three lines are flagged.

  4. =SUMPRODUCT((MOD(ROUND(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2))
    =SUMPRODUCT((MOD(ROUND(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2))

    Adds up the 0s and 1s: 0 + 0 + 1 + 1 + 0 + 0 + 0 + 1 = 3. That is the number of flagged lines.

  5. =SUMPRODUCT((MOD(ROUND(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2)*ABS(E2:E9))
    =SUMPRODUCT((MOD(ROUND(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2)*ABS(E2:E9))

    Multiplies each flag by the absolute amount and adds: 96,000 + 25,000 + 15,000 = 136,000. This is the total value of the flagged lines, regardless of debit or credit.

  6. =FILTER(A2:E9, (MOD(ROUND(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2), "No round-sum lines")
    =FILTER(A2:E9; (MOD(ROUND(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2); "No round-sum lines")

    FILTER keeps the rows where the flag is 1: lines 3, 4 and 8 with all five columns, 3 rows by 5 columns. If no line is flagged, it shows the message instead of #CALC!.

Step by step

  1. ABS(E2:E9) removes the sign, and ROUND(..., 2) cleans the decimals: 18,40018.400, 52,30052.300, 96,00096.000, 25,00025.000, 3,0003.000, 41,20041.200, 12,500.5012.500,50, 15,00015.000.
  2. MOD of each value by G1 (1,0001.000): 18,40018.400 gives 400, 52,30052.300 gives 300, 96,00096.000 gives 0, 25,00025.000 gives 0, 3,0003.000 gives 0, 41,20041.200 gives 200, 12,500.5012.500,50 gives 500.50, 15,00015.000 gives 0. Remainder = 0 is TRUE for lines 3, 4, 5 and 8.
  3. ABS(E2:E9)>=G2 tests against the minimum of 5,0005.000: TRUE for every line except line 5 (3,0003.000).
  4. Multiplying the two tests gives 0, 0, 1, 1, 0, 0, 0, 1. Line 5 drops out because it is below the minimum, even though 3,0003.000 is a multiple of 1,0001.000.
  5. FILTER keeps lines 3, 4 and 8: entry 25-0833 (-96,00096.000), 25-0590 (25,00025.000) and 25-0852 (15,00015.000). The result spills from F4 as 3 rows by 5 columns.
  6. Count and total with the same test inside SUMPRODUCT: 0 + 0 + 1 + 1 + 0 + 0 + 0 + 1 = 3 lines, and 96,00096.000 + 25,00025.000 + 15,00015.000 = 136,000136.000 in absolute amounts. The signed total would be -96,00096.000 + 25,00025.000 + 15,00015.000 = -56,00056.000, which hides the size of the credit.

Why it works

MOD with a divisor of 1,0001.000 returns 0 only when the amount divides by 1,0001.000 without a remainder, and ABS makes debits and credits behave the same. Multiplying two TRUE/FALSE tests works as AND, so FILTER receives one column of 0s and 1s.

Because the multiple and the minimum sit in cells, you can retest at 500 or 10,00010.000 without editing the formula.

FILTER needs Microsoft 365 or Excel 2021 or later; the count and total with SUMPRODUCT also work in older versions.

Watch out for

  • Floating-point trap: a computed amount such as 4,000.104.000,10 + 4,000.204.000,20 - 0.30 shows as 8,000.008.000,00 but can be stored as 7,999.9999999999997.999,999999999999. MOD of that by 1,0001.000 returns about 999.999999999999, not 0, so a round amount is missed. ROUND(..., 2) before MOD turns it back into 8,0008.000 first.
  • Leave ABS out of the minimum test and credits drop out: -96,00096.000 is not greater than or equal to 5,0005.000, so the revenue correction would be missed. MOD itself copes with negatives here, but the comparison does not.

  • If G1 is empty or 0, MOD divides by zero and the formula returns #DIV/0!.

    A round amount is an indicator, not proof: note why each flagged line is acceptable or follow it up.

Variations

=IF(AND(MOD(ROUND(ABS(E2), 2), $G$1)=0, ABS(E2)>=$G$2), 1, 0)
=IF(AND(MOD(ROUND(ABS(E2); 2); $G$1)=0; ABS(E2)>=$G$2); 1; 0)

Helper column for older Excel versions without FILTER: type it in L2, fill down to L9 and filter on 1. The flags are 0, 0, 1, 1, 0, 0, 0, 1 as in the main formula.

=SUMPRODUCT((MOD(ROUND(ABS(E2:E9), 2), G1)=0)*(ABS(E2:E9)>=G2)*ABS(E2:E9))
=SUMPRODUCT((MOD(ROUND(ABS(E2:E9); 2); G1)=0)*(ABS(E2:E9)>=G2)*ABS(E2:E9))

Only the total of the flagged lines, without listing them: 136,000136.000. It works in every Excel version.

All combinations