+- Forums (https://forum.compusoftdevelopment.com)
+-- Forum: Forums (https://forum.compusoftdevelopment.com/forumdisplay.php?fid=1)
+--- Forum: Tutorials & Guides (https://forum.compusoftdevelopment.com/forumdisplay.php?fid=6)
+--- Thread: Formulas & Currency Conversions (/showthread.php?tid=15)
Formulas & Currency Conversions - synoptix - 10-16-2019
Desktop Client Guide
Formulas
A formula allows you to perform a calculation and place the result into a cell. For our purposes we refer to this as a Formula Cell, however in practice formulas are actually entered into Description Cells. As a general rule formulas are entered into the spreadsheet when you are modifying it, and only calculate when you run the report.
An example of where a formula could be used would be if you wanted to sum a column of balances. The balance can be manually entered numbers or values retrieved from the database.
Components
There are three basic components to a formula.
Reference: A reference retrieves a value from another cell (not the actual cell the formula is in). The syntax for a reference cell is column id then row id. For example the cell in the top left corner of a spreadsheet has an id of A1.
Operators: Operators are symbols that perform an arithmetic function. Examples of operators include + - / *.
Functions: Functions are predefined operations that may perform multiple calculations. An example of this is SUM or AVERAGE. To use a function you need to enter the function followed by an open and close parentheses. The information enclosed in the parentheses is commonly referred to as the argument.
Syntax
Without Functions: When building a formula without a function you always start with an equal sign, usually alternate between a reference and an operator, and end with a reference. For example, to add the values of two cells together you could use the following:
=A1+B1
With Functions: When building a formula with a function you always start the formula with an equal sign, followed by the function, then the arguments. For example, to sum the values of several cells in a column you could use the following:
=SUM(A1:A10)
Cell references used in the argument are separated by a colon or a comma.
Examples
Formula
Description
=A1-B1
Subtract the value of B1 from the value of A1.
=SUM(B3:B10)
Sum the values in cells B3 through B10.
=B3/B7
Divide the value of B3 by the value of B7.
=(B2+B5)*C3
Add cells B2 and B5 then multiple the results by C3.
=SUM(A2:A10)-C13
Sum the values in cells between A2 and A10 then subtract C13 from that sum.
Functions
To find a list of recognized functions, open a report (modify), then click the Help button, identified by a red question mark on the toolbar. This displays a menu with Formula Help.
[attachment=428]
Choosing Formula Help displays a dialog box.
[attachment=429]
Clicking the function name on the left side of the screen displays the use of the function.
Entering Formulas
You can either enter formulas manually by typing the information, or by using the point and click method. When building an actual you report you will likely use a combination of both.
This example will display the difference between the Current YTD and Previous YTD balances. The spreadsheet is pictured below:
[attachment=431]
For this example, enter the formula =D6-E6 in cell F6.
Manual Entry: Click on cell F6, then enter the formula =D6-E6 and press the Enter key.
Point and Click Entry: Rather then entering the cell references by typing, enter them by clicking on the appropriate cell. To do this highlight cell F6 and press the equal sign.
[attachment=425]
Click on cell D6 using the mouse, or arrow to cell D6 using the arrow keys on the keyboard.
[attachment=433]
Doing this enters the cell reference of D6 after the equal sign. Next, press the Minus key.
[attachment=434]
To complete the formula, click on cell E6 then press the Enter key. Please Note: When creating formulas you must press the Enter key when finished. This action tells the software you are finished building the formula.
[attachment=427]
Currency Conversions
Purpose
The Currency Maintenance window allows you to enter conversion rates from one currency to another by rate type. These conversion rates are used when chosen in a report.
To get to the Currency Maintenance Screen, click Financial in the Maintain group, then click Currency Conversions.
[attachment=426]
Field Definitions
Conversion Type:
Month Average: is used with those conversions where the reporting period is a single month.
Transaction: is used with any account where you want the conversion to take place based upon an actual exchange rate for a specific date.
Year Average: is used for those types of transactions where the entire balance should be converted based upon a yearly average.
Static: is used to enter a single conversion rate that never changes.
From: The currency from which to convert.
To: The currency to which you want to convert.
Effective Date: The date of the conversion.
Rate: The rate of the conversion based upon the effective date (from USD to EUR on 7/10/06 1 USD = .785112 EUR rate would be .785112).
This guide applies to Synoptix versions 7 and 8