Excel track · Exercise 85 of 86 Paid

A monthly close checklist in formulas

Flag a reconciliation from its variance, then count the rows still out.

Advanced

Four close reconciliations, each with a variance to judge

Read-only data · screen 1 of 2 · answer in C2

In C2, flag the first reconciliation ready when its variance is zero and check otherwise.

	A	B	C
1	Check	Variance	Status
2	bank	0
3	accruals	12.5
4	prepaids	-3
5	payroll	0

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In C2, count the reconciliations whose variance is not zero.

	A	B	C
1	Check	Variance	Status
2	bank	0
3	accruals	12.5
4	prepaids	-3
5	payroll	0

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

IF tests a condition and returns one of two values, so a checklist row can answer in words. The formula =IF(B2=0,"ready","check") reads the variance and gives ready at zero, check at anything else. Four variances can be read by eye; forty cannot.

The bank row sits at zero, so it reads ready, and so does payroll. Accruals at 12.5 and prepaids at -3 come back as check. The second screen counts the rows still out with COUNTIF(B2:B5,"<>0"), and the answer is 2.

The criterion goes in quotes because a comparison is written as text. COUNTIF reads <>0 as not equal to the number 0. Drop the third argument and the failing row shows FALSE, which reads as a fault in the formula. Both outcomes belong in the call.

Syntax

=IF(variance=0,"ready","check") flags one check; =COUNTIF(range,"<>0") counts the rest

Worked example

On Four close reconciliations, each with a variance to judge, =IF(B2=0,"ready","check") returns ready.

=IF(B2=0,"ready","check")

Common errors

Error codes for IF returning a word instead of a number, then COUNTIF with a not-equal criterion
Error	What it means and how to fix it
`#DIV/0!`	A variance is divided by zero before the comparison is made.
`#NAME?`	The function is typed as IF or COUNTIF with a letter missing, and Excel returns #NAME?.
`Wrong flag`	The two branches are swapped, so a variance of zero reads check.

At work

Turning a column of close variances into a ready or check flag, then counting the rest.