LineAverageRound

LineAverageRound

This function returns the LineAverage in thousands. Its usage and parameters are the same as the LineAverage function, with the exception of the Company parameter*. The LineAverage function is called then the following calculations are performed on the result:

the value returned is rounded to the nearest thousand
then divided by one thousand
then the AvgBal User Report Adjustment is applied

LineAverageRound(UserRpt, LineCode, AvgBalDate, PosNeg, StartDate)

%fineprint{*NOTE: The Company parameter is not supported for this function because it would require User Report Adjustments to be made on a company by company basis, which would add too much complexity to the process.}%

Example Usage

=LineAverageRound("BALCON", "A01", "7/31/2009") Returns the daily average balance in thousands for line A01 of the BALCON user report during the month of July, 2009.
=LineAverageRound("BALCON", "A01", "3/17/2009", "C") Returns 'Debit Balance' because the daily average balance for line A01 of the BALCON user report during the month of March 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.
=LineAverageRound("BALCON", "A01", "6/30/2009", , "1/1/2009") Returns the daily average balance in thousands for line A01 of the BALCON user report during the first two quarters of 2009.
- Note the extra comma between the AvgBalDate and the StartDate. It must be included because the PosNeg parameter is not specified.

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

AvgBalDate

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

Optional Parameters

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

StartDate

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

Table of Contents