| Both sides previous revision Previous revision Next revision | Previous revisionLast revisionBoth sides next revision |
| gl_excel:lineaverageround [2010/06/18 21:42 UTC] – mwolfe | gl_excel:lineaverageround [2010/06/21 15:47 UTC] – removed reference to unavailable Company parameter mwolfe |
|---|
| ====== LineAverageRound ====== | ====== LineAverageRound ====== |
| This function returns the [[LineAverage]] in thousands. Its usage and parameters are the same as the LineAverage function. The LineAverage function is called then the following calculations are performed on the result: | 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 | * the value returned is rounded to the nearest thousand |
| * then divided by one thousand | * then divided by one thousand |
| * then the AvgBal adjustment is applied | * then the AvgBal User Report Adjustment is applied |
| |
| %InlineCode{LineAverageRound(UserRpt, LineCode, AvgBalDate, //Company//, //PosNeg//, //StartDate//)}% | %InlineCode{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 ==== | ==== Example Usage ==== |
| * %InlineCode{=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. | * %InlineCode{=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. |
| * %InlineCode{=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. | * %InlineCode{=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. |
| * Note the extra comma between the AvgBalDate and the PosNeg parameter. That must be included because the Company parameter is not specified. | * %InlineCode{=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. |
| * %InlineCode{=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. |
| * Note the two extra commas between the AvgBalDate and the StartDate. They must be included because the Company and PosNeg parameters are not specified. | |
| |
| ==== Required Parameters ==== | ==== Required Parameters ==== |
| {{section>parameters#avgbaldate}} | {{section>parameters#avgbaldate}} |
| ==== Optional Parameters ==== | ==== Optional Parameters ==== |
| {{section>parameters#company}} | |
| {{section>parameters#posneg}} | {{section>parameters#posneg}} |
| {{section>parameters#startdate}} | {{section>parameters#startdate}} |
| |
| |