There are two ways to add formulas to your Microsoft® Excel® worksheet using the Report Designer Add-In Task Pane; you can either drag-and-drop the formula from the Task Pane onto your worksheet or you can type the formula name directly into the cell. You can also edit formulas in two ways and you can reverse negative signs. In this tip you will learn how to add and edit formulas in your Excel worksheet using the Report Designer Add-In.
Applies to:
Sage 100 ERP Intelligence Reporting
Sage 300 ERP Intelligence Reporting
Sage 500 ERP Intelligence Reporting
Formulas
Adding Formulas
There are two ways to add formulas to your Microsoft® Excel® worksheet.
• Select the desired formula from the Task Pane. Drag and drop the formula onto your Microsoft® Excel® worksheet.
OR
• Type the formula name directly into the cell.
Editing Formulas
There are two ways to edit the formulas.
• The formula settings (function arguments window) can be accessed by clicking on the cell containing the formula and then clicking the fx button.
The formula parameters provided in the Function Arguments window will be used to specify what data is retrieved by the formula. Each setting serves as a filter to retrieve the data. The filter is applied in the order that the settings are displayed.
In the following GLActual300 formula example, Account (used to reference the account code from the Accounts or Accounts lists in the Task Pane) is applied first, followed by Year, Period, Company, AccountCategoryCode and finally AccountGroupCode in that order.
Ranges, mathematical calculations and wildcards can be used in the Account setting to filter on account numbers or main account codes.
• Formulas can be edited manually if you are familiar with the format of the formula.
Select the cell which contains the formula and then click within the formula bar and make your changes.
Reversing a Negative Sign
To change the sign of an account to a negative number, add a minus sign (–) to the beginning of the formula.
Drag the fill handle down to copy these to other accounts requiring the same change.
