Reporting on Current and Prior Year
When creating comparison reporting often it is necessary to bring in prior year information. If the value for the year is placed in each column, BizInsight functions will read a different value for each year. To make this dynamic, the year value would be a formula referencing the Report Year Parameter. The current year would be equal to the value of the report parameter and the prior year would equal the report parameter minus one.
Download PDF for full details
Grand Totals on Reports
Often when performing a grand total of many subtotals, the subtotal rows are added together individually. This technique creates the risk of the grand total being incorrect when new rows or subtotals are inserted.
In Excel 2010 and 2013 a new function was added called AGGREGATE, which will add all rows and not include prior subtotals.
Click here for more details.
Retrieving Formatted Dates
Instead of using VLOOKUPs to return a month name like "AUGUST" or a formatted date such as August 31, 2015, using the Text and Date function in combination can achieve some very cool results.