Function List

This page provides a description of all of the parameters that are used throughout the GL functions in Excel.

Use quotes if you are using the actual data in the formula: =Balance("SC11","12/31/2009")
Don’t use quotes if you are referring to the cell that holds the data: =Balance(A1,B1)
- Cell A1 would then contain SC11 not "SC11"
- Cell B1 would then contain 12/31/2009 not "12/31/2009"

Please note that all examples given on this page show quoted data, as would be used if the values were directly entered into the formula. However, it is generally better practice to enter the data in cells of their own and refer to those cells from within the formulas.

The GL account number or TFR line number. This must include any applicable company abbreviations.

"1010-000"
"1010-000CSB"
"SC110"
"SO11"

%fineprint{Used in: AvgBal, Balance, Budget, Description}%

The date that represents the month and year for which to compute the daily average balance (when used without the optional StartDate parameter).

"3/31/2009"    Returns the daily average balance for 3/1/2009 through 3/31/2009, inclusive
 "3/1/2009"    Returns the daily average balance for 3/1/2009 through 3/31/2009, inclusive
"3/17/2009"    Returns the daily average balance for 3/1/2009 through 3/31/2009, inclusive

The date that represents the month and year through which to compute the daily average balance (when used with the optional StartDate parameter).

"3/31/2009", with StartDate of  1/1/2009    Returns the daily average balance for 1/1/2009 through 3/31/2009, inclusive
 "3/1/2009", with StartDate of 1/31/2009    Returns the daily average balance for 1/1/2009 through 3/31/2009, inclusive
"3/17/2009", with StartDate of 1/12/2009    Returns the daily average balance for 1/1/2009 through 3/31/2009, inclusive

%fineprint{Used in: AvgBal, FedRptAvgBal, LineAverage, LineAverageRound}%

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}%

The date that represents the month and year used to retrieve the budget numbers

"3/31/2009"    Returns the budget for March 2009
 "3/1/2009"    Returns the budget for March 2009
"3/17/2009"    Returns the budget for March 2009

%fineprint{Used in: Budget, FedRptBudget, LineBudget, LineBudgetRound}%

The date of the user report column created by going to Edit Columns from the Edit User Report form in the General Ledger. The date must be manually added in the General Ledger in order for it to work in the Excel function.

"12/31/2009"
 "5/31/2009"

%fineprint{Used in: LineColumn}%

Name of the user report column created by going to Edit Columns from the Edit User Report form in the General Ledger. The column must be manually added in the General Ledger in order for it to work in the Excel function.

"AVG BAL"
"QTD Average Balance"
"YTD Avg Bal (Month End Adjusted)"

%fineprint{Used in: LineColumn}%

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}%

The 8-character alphanumeric Call Report concept code.

"RCON0010"
"RIAD4107"

%fineprint{Used in: CallRptDescription, CallRptValue}%

The 3-digit user report line item code.

"A01"
"C15"

%fineprint{Used in: LineAverage, LineAverageRound, LineBalance, LineBudget, LineBudgetRound, LineColumn, LineDescription, LineRound}%

The abbreviated federal report line number.

"SC11"
"SO141"
"RI-4435"

%fineprint{Used in: FedRptAvgBal, FedRptBalance, FedRptBudget, FedRptDescription}%

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}%

The month-end date that corresponds to the respective Call Report reporting period. Data must exist in the G&B Call Report program in order for a given date to return values.

"12/31/2013"
 "1/31/2013"

%fineprint{Used in: CallRptValue}%

The name of the federal report (not its description)

"Call"
"TFR"

%fineprint{Used in: FedRptAvgBal, FedRptBalance, FedRptBudget, FedRptDescription}%

The date that represents the month and year from which to compute the daily average balance (used only with the required AvgBalDate parameter).

 "1/1/2009", with AvgBalDate of 3/31/2009    Returns the daily average balance for 1/1/2009 through 3/31/2009, inclusive
"1/31/2009", with AvgBalDate of  3/1/2009    Returns the daily average balance for 1/1/2009 through 3/31/2009, inclusive
"1/17/2009", with AvgBalDate of 3/12/2009    Returns the daily average balance for 1/1/2009 through 3/31/2009, inclusive

%fineprint{Used in: AvgBal, FedRptAvgBal, LineAverageRound}%

The name of the user report (not its description)