LineBalance
This function returns the balance of a user report line as of a certain date.
LineBalance(UserRpt, LineCode, BalanceDate, Company, PosNeg)
Example Usage
- =LineBalance("BALCON", "A01", "7/31/2009") Returns the balance for line A01 of the BALCON user report as of July 31, 2009.
- =LineBalance("BALCON", "M02", "12/31/2009 8:00PM") Returns the balance for line M02 of the BALCON user report following the 2009 year end closeout.
- =LineBalance("BALCON", "A01", "3/17/2009", , "C") Returns 'Debit Balance' because the balance for line A01 of the BALCON user report on March 17, 2009 is a debit balance and the "C" in our formula told Excel to only display the balance if it was a credit. See below for more information about the PosNeg parameter.
- Note the extra comma between the BalanceDate and the PosNeg parameter. That must be included because the Company parameter is not specified.
- =LineBalance("BALCON", "A01", "3/17/2009", "Cit") Returns the balance for the Citizens Savings Bank accounts in line A01 of the BALCON user report (ie, it excludes all of the service company accounts). See below for more information about the Company parameter.
Required Parameters
UserRpt
The name of the user report (not its description)
"BALCON" "INCST2"
%fineprint{Used in: LineAverage, LineAverageRound, LineBalance, LineBudget, LineBudgetRound, LineColumn, LineDescription, LineRound}%
LineCode
The 3-digit user report line item code.
"A01" "C15"
%fineprint{Used in: LineAverage, LineAverageRound, LineBalance, LineBudget, LineBudgetRound, LineColumn, LineDescription, LineRound}%
BalanceDate
The date used to retrieve account balances from the General Ledger. End of year closeout transactions are posted as of 6:00 PM on December 31. A balance date of 12/31 will return balances for income and expense accounts, while a balance date of 12/31 8:00PM will return zero for all income and expense accounts and reflect an accurate year-end close out equity fund balance.
"1/31/2009" Account balances as of the end of the day on January 31, 2009 "12/31/2009" Account balances prior to posting the year-end close out batch (ie, income + expense > 0) "12/31/2009 8:00 PM" Account balances after posting the year-end close out batch (ie, income + expense = 0)
%fineprint{Used in: Balance, FedRptBalance, LineBalance, LineRound}%
Optional Parameters
Company
The company suffix code used to restrict user report numbers to a single company. To include all companies, either 1) do not enter anything for this parameter or 2) use the abbreviation "Con" (for Consolidated). To include only the default Citizens company, use either 1) double quotes with nothing inside: "" or 2) the abbreviation "Cit" (for Citizens).
Consolidated "Con" Consolidated "" Citizens Savings Bank "Cit" Citizens Savings Bank "CSA" CSA Services, LLC "CSB" CSB Services, Inc.
%fineprint{Used in: FedRptAvgBal, FedRptBalance, FedRptBudget, LineAverage, LineBalance, LineBudget}%
PosNeg
Limit results to Positive, Negative, Debit, or Credit only. You may spell out or abbreviate Positive, Negative, Debit, and Credit if you would like to make your formula more readable; the function performs a match on the first character only.
When evaluating numeric formulas such as SUM, Excel treats text as 0. This makes it possible to use this PosNeg parameter to include results of GL formulas in your other Excel formulas only in specific circumstances.
"P" Show balance only if positive, otherwise display: Neg Balance "N" Show balance only if negative, otherwise display: Pos Balance "D" Show positive number if debit balance, otherwise display: Credit Balance "C" Show positive number if credit balance, otherwise display: Debit Balance
%fineprint{Used in: AvgBal, Balance, FedRptAvgBal, FedRptBalance, LineAverage, LineAverageRound, LineBalance, LineRound}%