​
Overview

Using an IF statement, it’s possible to display a general ledger account under assets or liabilities based on whether its amount is positive or negative. This approach is most useful when it’s not feasible to create separate accounts for positive and negative balances in your ERP.

If possible, resolve this “at the source” (your accounting system) by creating two accounts – one for assets and one for liabilities – and moving amounts between them based on the sign. If that can’t be done, follow the steps below.

​
Basics

Follow these steps to set up formulas that display amounts correctly under assets and liabilities:

1

Separate the GL account

Temporarily remove the relevant account from your main balance sheet and place it in a separate sheet. This makes it easier to reference its amounts in future steps.

2

Add a row on the opposite side

If the original row was under assets, create a corresponding row under liabilities (or vice versa). These two rows will work together to display amounts correctly.

3

Create IF statements

Assign values to each row using conditional formulas. For instance:

// Asset row: Show positive amounts
if("Bank account"[0]>0,"Bank account"[0],0)

// Liability row: Show negative amounts
if("Bank account"[0]>0,0,-"Bank account"[0])
4

Extend to other periods

Apply these formulas from a period far enough back to capture all relevant data, and ensure they continue for any future periods you plan to forecast.

Was this page helpful?

Retained earningsCash flow check