Flag items above the materiality threshold (ABS)
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
Test case
1
Reset
Submit
A
B
C
D
E
F
G
1
2
3
4
5
6
7
8
9
10
○ Ready Enter formula in highlighted cells, then click Submit.
The problem
Problem statement
The threshold is in E1. Put "Review" or "Ignore" for the journal line in B2 in the Flag cell E2, where anything at or above the threshold in absolute value needs review.
Size matters, not sign — a large credit is as material as a large debit.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | D | E | |
|---|---|---|---|---|
| 1 | Journal line | Amount | Threshold | 5000 |
| 2 | J-101 | 6000 | Flag | |
| 3 | J-102 | 4000 | ||
| 4 | J-103 | -5000 |
Notes
ABS removes the sign before comparing, so a −5,000 line is treated the same as +5,000. Comparing the raw amount would let every credit slip through, which is the failure mode auditors find in sampling tests.
How to approach
Your answer goes in E2.
- Target cell is E2
- Take the absolute value first
- Compare with E1, inclusive
- Shape: =IF(ABS(?)>=?, ?, ?)
Function guide
FAQ
How do I test materiality in Excel?
=IF(ABS(amount)>=threshold, "Review", "Ignore"). The ABS is what stops credits from escaping the test.
Should the threshold be inclusive?
That is a policy decision. Decide it with the auditors and keep it consistent; the formula simply has to match what the policy says.