@database FinalCalc.hyper @font topaz.font 8 @remark Warning! Do not edit this file! @index Main @$VER: FinalCalc.part3.guide 1.04 (14 February 1996) @author "Khalid Aldoseri" @(c) "Copyright © 1995-1996 Khalid Aldoseri" @node Main "Formulas..." @toc "FinalCalc:guide/main.guide/Main" @{b}@{u} Formulas: @{uu}@{fg highlight} @{" > " link part3.1} Creating Formulas @{" > " link part3.2} Editing a Formula @{" > " link part3.3} The Recalc System @{" > " link part3.4} Range Names @{" > " link part3.5} Trace Cell @{" > " link part3.6} Analyzing Recalc Errors @{" > " link part3.7} Solve Problem @endnode @node part3.1 "Creating Formulas..." @toc "Main" @{b}@{u} Creating Formulas: @{uu}@{fg highlight} @{" > " link part3.1.1 } The Formula @{" > " link part3.1.2 } Formula Objects: Constants @{" > " link part3.1.3 } Formula Objects: Operators @{" > " link part3.1.4 } Formula Objects: Functions @{" > " link part3.1.5 } Formula Results @{" > " link part3.1.6 } Infinity @{" > " link part3.1.7 } Formula Comments @{" > " link part3.1.8 } Pasting a Formula @{" > " link part3.1.9 } Removing Formulas @{" > " link part3.1.10} Repairing Formulas after Deleting, Inserting or Moving @{" > " link part3.1.11} Finding ERROR and CIRC ERROR cells @endnode @node part3.1.1 "The Formula..." @toc "part3.1" @{b}@{u}The Formula:@{ub}@{uu} A formula is a mathematical expression entered into a cell. This expression can use constants (e.g. a number or a reference to another cell in the project), operators (such as addition, subtraction, division or multiplications) and functions (tools that perform complex operations) to generate calculated values. A formula always starts with an equals sign '=' and contains at least one constant, and possibly more constants separated by operators. The formula is evaluated by FinalCalc and the resulting value is then displayed in the cell. For example: =12/7 The value of 12 divided by 7. =(A3/100)+17 The value of cell A3, divided by 100, plus 17. Formulas can also use functions that accept constants and return a value. These functions are used to perform mathematical, financial, statistical or other operations. =SUM(A1:A10) The sum of all cells in the range A1 to A10. =COS(12) The cosine of 12. Formulas can also accept string constants and operators. ="Hi! "&A1 The string "Hi! " plus the string contents of cell A1. @endnode @node part3.1.2 "Formula Objects: Constants..." @toc "part3.1" @{b}@{u}Formula Objects: Constants:@{ub}@{uu} Constants in formulas can be either a static value (like a number or a string) or a variable value (like a cell reference). FinalCalc supports the following types of constants in formulas: @{b}@{u}@{fg highlight}Numeric Constants:@{ub}@{uu}@{fg text} A numeric constant is simply a number. The largest numeric constant accepted is 1.79E+308 (i.e. 179 followed by 306 zeros) and the smallest numeric constant accepted is 2.2E-308 (i.e. 0. followed by 307 zeros and then 22). Numeric constants can be entered in any of the following formats: @{b}@{fg highlight} Normal decimal numbers:@{ub}@{fg text} Any simple number, positive or negative, with or without a fraction. (e.g. 1, 12, 12.7, -17.01) @{b}@{fg highlight} Scientific notation:@{ub}@{fg text} Any decimal number, described in Scientific notation. This is a number between 0 and 1.0, then an 'E', an optional positive or negative sign, and then an exponent of 1 to 3 digits. Basically, the first number is multiplied by 10 to the power of the exponent. 1.0E+6 = 1.0 * 10^6 = 1,000,000 2.74E+5 = 2.74 * 10^5 = 274,000 1.0E-6 = 1.0 * 10^-6 = 0.000001 @{b}@{fg highlight} Percentage:@{ub}@{fg text} Any number that is followed by a '%' percent sign is divided by 100. (e.g. 15% is 0.15) @{b}@{fg highlight} Hexadecimal numbers:@{ub}@{fg text} You can enter hexadecimal numbers in formulas by preceding the number with '0x'. For example, 0x12abc is the decimal value of the hex number 12abc, which is 76476 in decimal. @{b}@{fg highlight} Binary numbers:@{ub}@{fg text} You can enter binary numbers in formulas by preceding the number with '0b'. For example, 0b100101 is the decimal value of the binary number 100101, which is 37 in decimal. @{b}@{fg highlight} ASCII values and characters:@{ub}@{fg text} You can enter the equivalent value of an ASCII character by entering that character surrounded by single quotes in a formula. For example, 'a' 'b' 'B' 'Z' .. etc. This is evaluated by FinalCalc to be the ASCII value of the character and not a string. e.g. ='A' returns 65 (which is the ASCII value of character A) @{b}@{u}@{fg highlight}String Constants:@{ub}@{uu}@{fg text} A string constant is a series of characters surrounded by double-quotes. The longest string constant allowed is 255 characters long. For example: "test" "Welcome home!" "You have made a bundle today!" @{b}@{u}@{fg highlight}Named Constants:@{ub}@{uu}@{fg text} FinalCalc has several constants that have fixed names. These constants are referenced by name in a formula, such as PI (which returns the value of 'pi' = 3.141592653589793). @{b}@{u}@{fg highlight}Cell References:@{ub}@{uu}@{fg text} A Cell Reference is the name of a cell in the current project, either in the current sheet, or in another sheet in the project. To reference a cell in the current sheet, use its column and row reference. (e.g. A1, D17, AD123). To reference a cell in another sheet, use its full sheet, column and row reference. (e.g. A_A1, B_D12) For example: =a1+(b2/12) ="Your name is"&C1 A cell reference in formulas can contain a '$' sign before the row and/or the column identifiers to indicate that the cell location is an absolute or fixed reference (e.g. $A$1, $A1, A$1). This does not affect calculations at all but affects how the paste operation works on formulas. See the section on @{"pasting a formula" link part3.1.8} for details. @{b}@{u}@{fg highlight}Range References:@{ub}@{uu}@{fg text} A Range Reference is the name of a range in the current project, either in the current sheet, or in another sheet in the project. FinalCalc only uses the value of the top left cell in a range, unless the range is being used in a function that expects a range. To reference a range in the current sheet, use its column and row references. (e.g. A1:B10, D10:F200). To reference a range in another sheet or multiple sheets, use its full sheet, column and row reference. (e.g. A_A1:A_B19, A_D10:Z_D10) For example: =sum(a_a1:c_b10) will give you the sum of ranges a1:b10 in sheets A, B and C. A range reference in formulas can contain a '$' sign before the row and/or the column identifiers. (e.g. $A$1:$B$10, $A1:$B1). This does not affect calcultions at all but affects how the paste operation works on formulas. See the section on @{"pasting a formula" link part3.1.8} for details. When entering a range in a formula, you can enter two periods '..' instead of the ':' colon characters. FinalCalc will automatically convert the '..' into a colon. @{b}@{u}@{fg highlight}Cells from another project on disk:@{ub}@{uu}@{fg text} You can access a cell that is in a project on disk in any formula. Just enter the project's file name within braces '{' and '}' and then the cell name. For example: ={FinalCalc:projects/forecast.sheet}B10 This will give you the value of cell B10 from the file "FinalCalc:projects/forecast.sheet". @endnode @node part3.1.3 "Formula Objects: Operators..." @toc "part3.1" @{b}@{u}Formula Objects: Operators:@{ub}@{uu} Operators specify an operation to perform on one or two constants, such as addition, subtraction or division and result in a new value. There are several types of operators in FinalCalc: @{b}@{u}@{fg highlight}Arithmetic Operators:@{ub}@{uu}@{fg text} These perform basic arithmetic operations. They work on numeric values only. They take two numeric values and return one numeric value in their place. + Addition - Substraction * Multiplication / Division ^ Exponentiation % Modulo (remainder of division) @{b}@{u}@{fg highlight}Comparison Operators:@{ub}@{uu}@{fg text} These compare two numeric or string values and return either a TRUE (1) or FALSE (0) value: = Equals > Greater than < Less than >= Greater than or equal to <= Less than or equal to <> Not equal to @{b}@{u}@{fg highlight}Logical Operators:@{ub}@{uu}@{fg text} These do logical and, or and not operations. They result in either TRUE (a value of 1) or FALSE (a value of 0): & Logical And: Takes two numbers. If both numbers are TRUE (non-zero), a TRUE will be returned, otherwise a FALSE will be returned. For example: 1 & 0 = FALSE 1 & 1 = TRUE | Logical Or: Takes two numbers. If either number is TRUE (non-zero), a TRUE will be returned, otherwise a FALSE will be returned if both values are FALSE. For example: 1 | 0 = TRUE 1 | 1 = TRUE 0 | 0 = FALSE ! Logical Not: Takes a single number and returns the reverse logical value of that number. If the number is TRUE (non-zero) it will return FALSE, and if the number is FALSE, it will return TRUE. For example: !1 = FALSE !0 = TRUE !TRUE = FALSE !FALSE = TRUE @{b}@{u}@{fg highlight}Text Operator:@{ub}@{uu}@{fg text} The '&' operator also doubles as a text joining operator. It takes two strings and returns a string that consists of the second string added to the end of the first string. FinalCalc solves formulas with more than one operator based upon the operator's priority level and the location of the operator in the formula. leading + or - ^ % * / + - = > < >= <= <> ! & | Operators are solved in the order above. If the operators are of the same priority, the then first operator found will be solved first (i.e. left to right). For example: 10+12/6 would get solved as: 12/6 = [2], then 10 + [2] The division get solved first since it has a higher priority than addition. Parantheses can be used in formulas to change the priority order. Any part of the formula surrounded by parantheses '(' and ')' is solved before the rest of the formula. You can nest as many parantheses as you like. In the previous example, if we had used (10+12)/6, the result would have been 3.667, which is 10+12 = [22], then [22] / 6. @endnode @node part3.1.4 "Formula Objects: Functions..." @toc "part3.1" @{b}@{u}Formula Objects: Functions:@{ub}@{uu} A formula Function is a special operater that takes a number of constants and returns a value. Basically, a function performs a specific operation that can be either a built-in formula, or a more complicated calculation, in order to make it easier for you to build formulas. For example, the SUM() function (one of the most popular functions) returns the sum of its arguments. SUM(A1:A5) is the same as A1+A2+A3+A4+A5. A function can be in one of two forms. If it doesn't need any arguments, you simply have to use its name, (e.g. RAND). If it needs arguments, then it needs to be followed immediately by an opening parantheses '(', the arguments, and then the closing paranatheses ')'. FinalCalc needs the parantheses to know the arguments that belong to the function. An argument can be a numeric or string constant or a cell or range reference. Each function expects different arguments in a specific order. @{"Appendix 1" link "FinalCalc:guide/appendix1.guide/Main"} contains a full list of all functions available, their format, arguments, input and output, and examples of their use. A function will return one of four possible results: a numeric value, a string value, a cell or range address, or an error. Some functions return a different result based on its input arguments. @endnode @node part3.1.5 "Formula Results..." @toc "part3.1" @{b}@{u}Formula Results:@{ub}@{uu} Once a formula is evaluated, its result will be one of the following: @{b}@{u}@{fg highlight}Numeric Result:@{ub}@{uu}@{fg text} Any valid decimal number. Remember that the number shown on the display can be different from actual result because of your selected format for the cell. @{b}@{u}@{fg highlight}String Result:@{ub}@{uu}@{fg text} A string of characters, up to 4096 characters long. @{b}@{u}@{fg highlight}Error:@{ub}@{uu}@{fg text} The formula has an error. The cell shows as 'ERROR' in the display. If a @{"circular error" link part3.3 77} occurred, the cell will show 'CIRC ERROR'. When a formula is shown as ERROR, the cell contents display will show the approximate position of the error highlighted. If the Error Line is active (use the Amiga-E hotkey to toggle it), it will give you a description of the error that occurred. @endnode @node part3.1.6 "Infinity..." @toc "part3.1" @{b}@{u}Infinity:@{ub}@{uu} FinalCalc supports Infinity as a valid numeric value. If you divide a number by zero, an infinity will result. This is shown as 'oo' in the display. Any formula operation that results in a number larger than 1.79E+308 will also result in an infinity. Negative Infinity is supported, too. This is shown an '-oo' in the display. Any formula operation that results in a number smaller than -1.79E+308 will result in a negative infinity. You can directly enter an infinity value in a formula by using the INF and NINF constants. (INF = positive infinity, NINF = negative infinity). @endnode @node part3.1.7 "Formula Comments..." @toc "part3.1" @{b}@{u}Formula Comments:@{ub}@{uu} You can add a comment to the end of a formula by using the ';' semi-colon character, and then any text you like. This does nothing at all other than show up when pointing to the cell or when editing the formula. For example: =sum(a10:a41);This gets the sum of last month's sales @endnode @node part3.1.8 "Pasting a Formula..." @toc "part3.1" @{b}@{u}Pasting a Formula:@{ub}@{uu} When pasting a copy of a cell that contains a formula into another cell, FinalCalc changes the cell and range references in the pasted formula in order to adjust its relative references to its new position. For example: Cell B1 contains the formula =A1+1. This basically means =(the cell to the left)+1 If you copy cell B1 and paste it into cell C1, the resulting formula in Cell C1 will be =B1+1, which also means =(the cell to the left)+1. FinalCalc automatically adjusts sheet, row and column references in all cell and range references when it pastes formulas. You can specifically instruct it not to adjust a column or row reference by preceding that column or row reference (in a cell or range reference) with a '$' locking character. The '$' lock can be used for both column and row, or either one in a cell. you can even mix and match it in a range. For example: The formula =$A$1 will always stay the same even when pasted into other cells at a different position. The formula =$A1 will not change the column value, but the row value will be adjusted when it is pasted to a different position. The formula =sum($A$1:A100) will not change the first part of the range ($A$1), but the second part (A100) will be changed when it is pasted to a different position. The Edit Paste Settings requester allows more control over how formulas are pasted, with the following options: @{b}@{u}@{fg highlight} Fix Offsets for Columns, Rows and Sheets:@{ub}@{uu}@{fg text} This defaults to on for all three. If you turn one of them off, FinalCalc will not adjust that part when pasting a formula. For example, if you turn off Fix Offsets for Columns, it will be the same as giving all formulas a '$' lock before all column references. @{b}@{u}@{fg highlight} Paste Values only:@{ub}@{uu}@{fg text} The formula is not pasted, only the result of the formula. @{b}@{u}@{fg highlight} Ignore '$' Locking:@{ub}@{uu}@{fg text} This defaults to off. If it is turned on, FinalCalc ignores the '$' lock when pasting and adjusts all references. @{b}@{u}@{fg highlight} Transpose Columns/Rows:@{ub}@{uu}@{fg text} When pasting a formula, the column and row offsets are swapped. i.e. if you are pasting a formula copied from cell A1 into cell B3 (+1 columns, +2 rows), the offset applied will be +2 columns, +1 rows. @endnode @node part3.1.9 "Removing Formulas..." @toc "part3.1" @{b}@{u}Removing Formulas:@{ub}@{uu} The Remove Formulas feature removes the actual formula from the current cell or range in the current sheet but leaves the formula's result in it, changing the cell into a numeric or string cell. To use this, select the cell or range you want to clear the formulas from and then use the 'Edit - Remove Formulas' menu. If a formula had an ERROR result, it will be converted to a numeric cell with a value of 0. @endnode @node part3.1.10 "Repairing Formulas after Deleting, Inserting or Moving..." @toc "part3.1" @{b}@{u}Repairing Formulas after Deleting, Inserting or Moving:@{ub}@{uu} When inserting or deleting a row, column, or a sheet in a formula, or moving a range in a sheet, FinalCalc has to repair all references to cells affected by the change. For example, you have a formula like this: =C10+1 and you insert one row at row 5. So, cell C10 has now become C11. FinalCalc will repair all references to cell C10 to become C11, so the example formula will become =C11+1. FinalCalc also adjusts range names, changing their addresses to reflect the change in the sheet. When adjusting range names during a move, if a range name is completely within the range being moved, it will be changed to reflect its new position, otherwise it will not be modified. @endnode @node part3.1.11 "Finding ERROR and CIRC ERROR cells..." @toc "part3.1" @{b}@{u}Finding ERROR and CIRC ERROR cells:@{ub}@{uu} You can quickly find if there are any formulas with errors in the current project by using the 'Actions - Select - Next ERROR cell' and 'Actions - Select - Next CIRC ERROR cell'. These move the cursor to the next cell in the project with an error or a circular error. If no cells were found, the cursor is not moved. @endnode @node part3.2 "Editing a Formula..." @toc "Main" @{b}@{u}Editing a Formula:@{ub}@{uu} Editing a formula is like editing a normal cell. Simply move the cursor to the cell you want to edit, and hit the Return key, or use the 'Edit - Edit' menu. @{b}@{u}@{fg highlight}Selecting a Cell or a Range:@{ub}@{uu}@{fg text} While editing a formula, you can move the sheet view cursor in two ways: @{b}@{fg highlight}Using the mouse:@{ub}@{fg text} Simply click on a cell, or drag-select a range. The cursor will move to where you point. The cursor will come back to the place it was after you are finished editing the formula. @{b}@{fg highlight}Using the keyboard: (the Roam mode)@{ub}@{fg text} You can move around in the sheet view via the keyboard cursor keys while editing a formula by invoking the 'Roam' mode. During Roam mode, the cursor keys and the keyboard range controls work exactly as if you were not editing a cell. There are several methods of invoking the Roam mode: - Use the 'Formulas - Toggle Roam Mode' menu. - Use the Amiga-M shortcut. - Use either the up or down cursor keys. FinalCalc will automatically drop into Roam mode and move the sheet view cursor in that direction. The Roam mode is indicated in the status indicator (at the top left of the sheet view). The following keys are active during the Roam mode: @{b} Cursors keys:@{ub} Left/right and up/down @{b} Shift-cursor keys:@{ub} Left/right and up/down one 'page'. @{b} Alt-cursor keys:@{ub} Left/right and up/down to limits. @{b} Control-cursor keys@{ub} Jump to end of block. @{b} TAB Key:@{ub} Range on/off. @{b} Shift-TAB Key:@{ub} Recall last range. Once you have selected a cell or a range in the sheet view, you can paste it into the current formula by using the Paste Cell/Range function (Amiga-V), which removes the current cell or range under the edit cursor and replaces it with the select cell or range. @{b}@{u}@{fg highlight}Auto-paste into a formula:@{ub}@{uu}@{fg text} While editing a formula, you can click on a cell or select a range using the mouse and that cell or range will be automatically pasted into the formula. If the edit cursor was pointing at a cell or range in the formula, that is removed and the new selection is inserted in its place. @{b}@{u}@{fg highlight}Formula Editing Tools:@{ub}@{uu}@{fg text} When editing a formula, a new set of menus show up instead of the usual FinalCalc menus. Use the mouse right button to view those menus. The following tools are available during formula editing: @{b}@{fg highlight}Find Matching Bracket: (Amiga-B)@{ub}@{fg text} The Find Matching Bracket function looks for matching parantheses in a formula. To use it, simply move the edit cursor to an open '(' or close ')' parantheses, and use the Find Matching Bracket menu. If there is a matching bracket for the current one, the cursor will be moved to it. This helps in seeing through multiple levels of brackets. @{b}@{fg highlight}Function Description: (Amiga-O)@{ub}@{fg text} The Function Description function gives a one-line description of a formula function and the arguments it expects. To use it, simply point at the function and use the Function Description menu. The description will show up below the formula. The Function Description is also 'live' while you are editing a formula. It automatically invokes when you type a function and then the '(' character. @{b}@{fg highlight}Function Help: (Amiga-H)@{ub}@{fg text} The Function Help function brings up a full description of a formula function. To use it, simply point at the function and use the Function Help menu. A guide window will open showing the help guide entry for that function. @{b}@{fg highlight}Paste Current cell/Range: (Amiga-P)@{ub}@{fg text} This simply inserts the current cell or range address into the formula. Select a cell or range in the sheet view and then use the 'Paste Cell/Range' menu. @{b}@{fg highlight}Paste Range Name: (Amiga-N)@{ub}@{fg text} This brings up a requester listing all the defined range names in the project allowing you to select one of them to paste into the formula. If there are other sheets in the current sheet with views open on them, the requester will also list the active cell or range in those views, allowing you to directly paste it into the current formula. @{b}@{fg highlight}Paste Function: (Amiga-F)@{ub}@{fg text} This brings up a requester that shows all math functions available in FinalCalc. You can choose one of them and it will be pasted into the formula. The requester has a box that shows the format of the function highlighted and also has a Details button that brings up the online help document for that function. If the function selected uses '()' parantheses, these will be added and the cursor will be set at the '(' character, ready for entering its arguments. The Function Description (as described above) is also automatically shown. @{b}@{fg highlight}Paste Filename: (Amiga-I)@{ub}@{fg text} This brings up a file requester. From it, you can select any file name. That file name is then pasted back into the formula, surrounded by '{' and '}' characters. (For use in @{"accessing cells from a project on disk" link part3.1.2 114}. (e.g. "FinalCalc:Projects/test.sheet") @{b}@{fg highlight}Paste Date String: (Amiga-Y)@{ub}@{fg text} Brings up the Date String Builder requester, which allows you to build a date string. When you are done, the string will be pasted into the formula surrounded by double-quotes. (e.g. "first saturday next month") @{b}@{fg highlight}Paste Time Format: (Amiga-T)@{ub}@{fg text} Brings up the Time Format String requester allowing you to select a time format. When you are done, the format will be pasted into the formula surrounded by double-quotes. (e.g. "hh:mm:ss") @{b}@{fg highlight}Paste Date Format: (Amiga-D)@{ub}@{fg text} Brings up the Date Format String requester allowing you to select a date format. When you are done, the format will be pasted into the formula surrounded by double-quotes. (e.g. "d Mmm yyyy") @endnode @node part3.3 "The Recalc System..." @toc "Main" @{b}@{u}The Recalc System:@{ub}@{uu} The Recalc System is the method FinalCalc uses to calculate the values of formulas in a project. The Recalc scans through the project looking at the cells that contains formulas that need solving, and evaluating the value of the formula in each cell, and then storing the result back into the cell. The Recalc is, by default, automatically invoked after each change to the project that may cause the value of a formula to change. The Recalc runs in several different modes. These modes are controlled by the Recalc Settings requester. @{b}@{u}@{fg highlight}Manual vs. Automatic Recalc:@{ub}@{uu}@{fg text} By default, the Recalc is always invoked after any change to the project's data. This makes sure that the values you see on the display correctly reflect the contents of the cells in the project. If you set Manual Recalc on (in the Edit Recalc Settings requester), then the Recalc will not be called at all unless you call it manually via the 'Actions - Calc Now' menu (Amiga '=' shortcut). FinalCalc will keep track of what cells have been changed since the last recalc for you so that it will only recalculate those when you invoke the Recalc manually. @{b}@{u}@{fg highlight}Background Recalc:@{ub}@{uu}@{fg text} The FinalCalc Recalc system runs in the background. When you edit a cell, for example, the Recalc is launched as a background process which re-evaluates the project. You can continue working and changing other cells in the project while this is happening. When a background recalc is done, FinalCalc looks again at what happened during that recalc, if you did more changes, it will go back and start another recalc, and will keep doing so until it has no more changes to calculate. FinalCalc will collect all the changes that happened between recalc runs and do them together at the next run. This means that the recalc runs only as many times as needed. While the background recalc is running, a 'Calc' indicator is shown on the FinalCalc screen's titlebar. There are cases, however, when FinalCalc does wait for the background recalc to end before performing an operation. These are operations that are likely to change the project data in a way that would damage the Recalc's knowledge of what the project looks like. These actions include inserting and deleting columns, rows or sheets, moving a cell or a range, removing formulas, and any other action that would cause multiple cells to change their formulas. FinalCalc also waits for the background recalc to end when saving or printing the project, this ensures that the correct values are saved and printed. You can turn off the Background Recalc in the Edit Recalc Settings requester. In that case, the recalc will always run in the foreground, and you will have to wait for it to end between actions. The Background Recalc, by default, runs at a lower priority than the rest of the program. This means that the user interface does not slow down while the recalc is running. You can, if you like, increase the priority of the background recalc process by turning off the 'Low Priority' option. This will cause the user interface to slow down a little (may not be noticable on fast machines) but will make the recalc run 10 to 20 percent faster. Changing the priority of the recalc is immediate, the recalc task is told to go up in priority right away, not at the next recalc. This is useful when another program is running on the system and is eating a lot of processor time, causing the background recalc task not to get any processor time. If FinalCalc has to wait for more than 2 seconds for the background recalc to end, a status window will show up informing you that is happening, and will allow you, if possible, to cancel your request if you don't want to wait until the recalc ends. FinalCalc will automatically change the priority of the recalc to high priority until the current recalc is done. @{b}@{u}@{fg highlight}Natural Recalc & Circular References:@{ub}@{uu}@{fg text} When FinalCalc evaluates a formula, it scans through the formula to see what other cells in the project it depends on, and solves those cells first. This ensures that the resulting value of the formula is correct. The Recalc can handle any complexity in formula relationships (even thousands of complex inter-dependancies) because of how it decides to solve which cell first. There are cases, however, when a cell references itself indirectly, causing what is commonly known as a 'Circular Reference'. That is, the value of the cell depends on itself, so there is no valid result for its formula. FinalCalc handles circular references in one of two ways: a. If the 'Circular Reference triggers ERROR' setting is on, that formula will have an ERROR result. b. If the 'Circular Reference triggers ERROR' setting is off, FinalCalc will solve the formula anyway, giving whatever result it gets back based on the old value of the cell. For example, if cell A1 contains the formula =A1+1, when you first enter the formula, this would result in a value of 1, since the previous value of A1 was 0. At the next change, it would get changed to 2 (1+1), and so on. @{b}@{u}@{fg highlight}Minimal Recalc:@{ub}@{uu}@{fg text} When a recalc is invoked, FinalCalc only recalculates the cells that have been changed since the last recalc was done. This the called a 'Minimal Recalc' since it only recalculates what needs to be recalculated, not the entire project. This has a major benefit of speeding up the recalc considerably. You can, however, force FinalCalc to evaluate every cell in the project during the recalc by turning off the Minimal Recalc option in the 'Edit Recalc Settings' requester. There are some formulas that need to be always recalculated because they contain a function that has a changing value. For example, the NOW function retuns the current time, so its value increases between recalcs, and the RAND function returns a random number, so it changes between recalcs. A formula of this type is called a 'Live' formula. (i.e. its value is always changing). Live formulas are indicated in the cell contents display by a (LIVE) indicator. The TODAY function is considered a live function, but is handled differently because its value only changes occasionally. Formulas containing the TODAY function are considered live only when the project is first loaded, and whenever the current system date changes from the last recalc. @endnode @node part3.4 "Range Names..." @toc "Main" @{b}@{u}Range Names:@{ub}@{uu} In a project, you can give a name to a cell or range. Once you define it, that name can be used anywhere in FinalCalc instead of the cell or range. The main use of this is in formulas, as described here. There are restrictions on what the range name can look like: a. You can only use uppercase letters. Lowercase letters are automatically changed to uppercase. b. The first letter of the name must be an alphabetic character. All other characters can be A-Z, 0-9 or '_'. c. When entering the range name, you can use the space character, but it will be converted to an underscore '_' character automatically. d. The maximum number of characters in the name are 20 chars. @{b}@{u}@{fg highlight}Creating a Range Name:@{ub}@{uu}@{fg text} To define a range name, highlight the range you wish to name in the sheet view and then use the 'Edit - Names - Define' menu. The 'Define Range Name' requester (shown below) will come up with the current range shown. Enter a name for the range, and click on 'Define'. If you define a name that already exists, the old name will be updated with the new defined range for it. Once the range name is defined, you can then use it in formula. For example, if you define the range 'B1:B12' as 'SALES'. You can then get the sum of that range by entering the formula '=SUM(SALES)', which is the same as '=SUM(B1:B12)'. In formulas, a range name is always considered as a range, even if it points to a single cell. @{b}@{u}@{fg highlight}The Range Names List:@{ub}@{uu}@{fg text} You can access the list of range names defined in the project by using the 'Edit - Names - Edit' menu. It will bring up the 'Edit Range Names' requester. This shows the range name and its defined value. From it, you can do the following actions: a. Edit a range name: Brings up the Edit Range Name requester (shown below). This shows the range name, its defined value, and the sheet it belongs to. You can edit any of the above. b. Define a range name: Brings up the Define Range Name requester, as described in 3.4.1 above. c. Delete a range name: Deletes a range name from the list. Since deleting a range name may affect the entire project, you are asked whether you want to do a full recalc of the project after deleting the range name. @{b}@{u}@{fg highlight}Make Names Table:@{ub}@{uu}@{fg text} The Make Names feature produces a three column table in the sheet that lists all the range names in the first column, and addresses in the second column, and the sheet they below to in the third column. The list is built starting with the current cell. Make sure that you have enough blank space before invoking this, as it will overwrite any previous data in the cells it uses. @{b}@{u}@{fg highlight}Read Names Table:@{ub}@{uu}@{fg text} The Read Names Table feature reads a three-column list from the sheet that contains a list of range names and their addresses and adds them to the current sheet's Names list. The table's format must be the same as the format created by Make Names Table, as described in 3.4.3 above. To use this function, first highlight the three columns and the rows you want read with the range, and then invoke the Read Names Table function from the 'Edit - Names - Read Table' menu. If an entry already exists in the range names list, it will be replaced with the new value. Also, if any entry is invalid or blank, it is simply ignored. Since this changes multiple range names which may affect the entire project, you are asked whether you want to do a full recalc of the project after the list is read into the range names list. Using the Make Names and Read Names features, you can dump the entire range names list to the sheet, do a lot of editing to them, and then read them back into the range names list. @endnode @node part3.5 "Trace Cell..." @toc "Main" @{b}@{u}Trace Cell:@{ub}@{uu} The Trace Cell function allows you to trace a cell's links with other cells by tracking down the formulas that depend on it, and the cells it depends on. To Trace a cell, move the cursor to that cell and use the 'Tools - Trace Cell' menu, which will bring up the Trace Cell requester which controls how the trace works. From it, you can control the following settings: @{b}@{fg highlight}Trace Child Cells:@{ub}@{fg text} If the 'Trace Child Cells' option is turned on, the trace will show all the cells in the project that depend on the value of the cell being traced. @{b}@{fg highlight}Trace Parent Cells:@{ub}@{fg text} If the 'Trace Parent Cells' option is turned on, the trace will show all the cells in the project that affect the value of the cell being traced. This option is not available if the cell being traced is not a formula. @{b}@{fg highlight}Show Results on Sheet:@{ub}@{fg text} If this option is turned on, the results of the trace are shown in the sheet view as different cell styles. Cells that depend on the current cell are marked as Bold in the sheet view, and cells that affect the current cell are marked as Underlined. These markers are kept on the sheet view until another trace is done. @{b}@{fg highlight}Keep Previous Results:@{ub}@{fg text} Keeps the results (bold and italic trace markers) from the previous trace on the sheet, adding the new results to them. @{b}@{fg highlight}Create Trace Report:@{ub}@{fg text} If the 'Create Trace Report' option is turned on, a detailed report of the trace is generated and is then shown in its own window when the trace is done. The trace report (shown below) shows the following data: a. The cell being traced (its name and contents). b. The number of parent and child cells found. c. A full list of each child cell found and its contents. d. A full list of each parent cell found and its contents. e. A list of range names that affect the cell. f. A list of range names that are affected by the cell. The trace report is kept in temporary storage as long as FinalCalc is still running. You can access previous trace reports by using the 'Tools - View Previous Reports' menu. This brings up a list of all stored reports allowing you to select the one to view. When the 'Trace' button is hit, the trace starts. A progress indicator window comes up showing what the trace is currently doing, and how many child and parent cells have been found. You can abort the trace at any time by hitting the Escape key or the Abort button. @endnode @node part3.6 "Analyzing Recalc Errors..." @toc "Main" @{b}@{u}Analyzing Recalc Errors:@{ub}@{uu} The Analyze Recalc Errors feature looks at all formula cells in the project and builds a report of all formulas that have errors. This report is then shown to the user in its own window. Each entry in the report shows the following information: a. The cell that has the error. b. A description of the error that occurred. c. The contents of that cell. d. A pointer marking the position of the error. To view the errors report, use the 'Tools - Analyze Recalc Errors' menu. This will start a full recalc of the entire project, and FinalCalc will monitor all the errors as the recalc progresses. The report is kept in temporary storage as long as FinalCalc is still running. You can access previous reports by using the 'Tools - View Previous Reports' menu. This brings up a list of all stored reports allowing you to select the report to view. @endnode @node part3.7 "Solve Problem..." @toc "Main" @{b}@{u}Solve Problem:@{ub}@{uu} The Solve Problem feature allows you to reverse-solve a problem by asking the program to find out what input value you would need to get a specific output. To access this, use the 'Tools - Solve Problem' menu. This will bring up the 'Solve Problem' requester. You have to define the following: @{b}@{fg highlight} a. Destination Cell:@{ub}@{fg text} This is the you want to change. It must be a numeric formula cell. @{b}@{fg highlight} b. Value for Destination Cell:@{ub}@{fg text} The value you want to achieve in the destination cell. This is a simple number. @{b}@{fg highlight} c. Optimize Using Cell: (Source Cell)@{ub}@{fg text} The cell you want to change to get the destination cell to the target value. This must be a numeric non-formula cell. @{b}@{fg highlight} d. Solve Accuracy:@{ub}@{fg text} The accuracy level required for the solve. This defaults to 0.0000001. @{b}@{fg highlight} e. Maximum number of tries:@{ub}@{fg text} The maximum number of tries before the solve attempt will stop if it finds a very complex problem. The default is 100. Once you have defined the above, click on 'Solve'. FinalCalc will bring up a status window showing the current attempt number, the maximum number of tries, and the current value of the source and destination cells. The Solve Problem will stop when it has met one of the following conditions: a. It found a solution that achieves the target value at the required accuracy. b. It arrived at a solution that is the closest that it can get while still somewhat away from the required target. This is recognized quickly and the solve attempt stops. c. It could not find a solution in the maximum number of tries. d. The source or destination cells arrived at an infinity value. e. It was aborted by the user hitting the Escape key or the Abort button. If the solve problem was successful (i.e. condition a. or b. occurred), the source cell will be changed to reflect the solution that was arrived at. @next "FinalCalc:guide/part4.guide/Main" @endnode @remark This node is only called from menu help @node part3.8 "View Previous Reports..." @toc "Main" @{b}@{u}View Previous Reports:@{ub}@{uu} Reports generated in FinalCalc by tools such as Trace Cell, Analyze Recalc Errors, Search All Matches, and File Compare are kept in temporary storage as long as FinalCalc is still running. You can access previous reports by using the 'Tools - View Previous Reports' menu. This brings up a list of all stored reports allowing you to select the report to view. @endnode