Transcription of Quattro Pro Formulas, Functions, and Macros
1 Quattro Pro Formulas, Functions, and Macros Charles M. Cork, III. March 27, 2018. Contents Introduction i The reason for this book .. i Conventions .. ii QP quirks, generally .. iii I Quattro Pro Formulas 1. 1 Basic Formulas 2. Relative v. absolute addresses .. 3. Relative ( R1C1 ) v. normal reference style .. 5. Combining text: Text (string) formulas .. 5. Combining text and the content of cells .. 6. How to set up a database showing interest compounding monthly .. 6. How to set monthly installments so that interest payments are equal for each payment .. 7. How to type the date of every Friday in a year in a column .. 7. 2 Block (Array) Formulas 9. Uses of block formulas .. 9. How to count or sum all of the numbers in a column that are less than a certain number .. 11. How to count all entries in a column that equal the data in this/each row . 11. How to count or sum all cells that meet two conditions .. 12. How to add a running total of all cells, or of only cells meeting a condition 12.
2 How to sum the numbers that come within a date range .. 13. How to get the average of numbers in a column that meet a condition .. 14. How to get the maximum and minimum number that meets a condition . 15. How to get the maximum value in a column on a certain date .. 15. How to get the minimum value in a column on a certain date .. 16. How to mark possible duplicate entries within a time range .. 17. II Quattro Pro Functions 18. 3 Conditional functions 20. @IF .. 20. @CHOOSE .. 20. i 4 The Properties of a Cell, Page, File, etc. 22. @CELL, @CELLPOINTER, @CELLINDEX .. 22. @IS** logical functions .. 24. @TYPE .. 25. @N, @S .. 25. @PROPERTY .. 25. @COMMAND .. 26. @FILEEXISTS .. 26. How (not) to test whether a cell is blank .. 26. How to identify the last non-blank cell in a column .. 27. How to enter multiple lines of text in a single cell .. 28. How to determine the width and height of a row in inches .. 28. Appendix: Cell (Active_Block) Properties .. 29.
3 Appendix: Worksheet (Active_Page) Properties .. 30. Appendix: Notebook Properties .. 31. Appendix: Application Properties .. 32. 5 The Coordinates of a Cell or Block 34. @@ .. 34. @ADDRESS .. 36. @OFFSET .. 37. Comparing @ADDRESS and @OFFSET .. 37. How to replace named blocks with @@ .. 38. How to get the attributes of a single cell by indirect reference .. 38. @BLOCKNAME, @BLOCKNAMES .. 39. 6 Functions for Text (Strings) 40. Creating text: @CHAR, @REPEAT .. 40. Deleting non-text: @CLEAN, @TRIM .. 40. Combining text: @CONCATENATE .. 40. Dividing text: @LEFT, @RIGHT, @MID, @FIELD .. 41. Modifying or deleting text: @SUBSTITUTE, @REPLACE, @SETSTRING . 41. Modifying the case of text: @UPPER, @LOWER, @PROPER .. 42. Other basic functions: @LENGTH, @FIND .. 42. How to count the number of instances of a particular character in a cell .. 42. How to count the number of words in a particular cell .. 43. How to return the last word in a series of words .. 43. How to return the last name in a string, ignoring suffixes.
4 46. How to generate a new file name based on the current notebook .. 47. 7 Math Functions 49. Adding: @SUM, @TOTAL, @SUBTOTAL, @SUMIF .. 49. Averaging: @AVG, @PUREAVG .. 50. Extremes: @MAX, @MIN, @PUREMAX &-MIN, @LARGEST, @SMALLEST 50. Determining relative rank: @RANK .. 50. Rounding/Trimming: @ROUND, @INT, @TRUNC .. 51. Remainders and Patterns: @MOD .. 52. How to return the difference between two numbers if positive, or zero if negative .. 52. How to return the difference between two numbers as a positive number . 52. How to mark the largest value in a column .. 53. How to compare value changes from last year when this year is not complete 53. How to sum every other number in a column .. 54. How to average the preceding three numbers in a column, not counting blanks.. 54. How to sum the contents of a column if rows or columns are inserted .. 56. 8 Date and Time Functions 58. Current date and time: @TODAY, @NOW .. 58. Dates and their components: @DATE, @YEAR, @MONTH, @DAY.
5 58. Information about a date: @DATEINFO .. 59. Days of the week: @WEEKDAY, @WKDAY .. 60. Comparing dates: @DATEDIF .. 60. Other date functions .. 61. Time and components: @TIME, @HOUR, @MINUTE, @SECOND .. 61. How to display a message after a certain time of the day .. 61. How to sum data by year .. 61. How to sum data by month and year .. 62. How to determine the Tuesday after or before a date .. 63. How to set up delivery tables .. 64. How to determine the number of Wednesdays in a given month .. 65. 9 Converting between Text and Numbers 68. Converting numbers to generic number-like text: @STRING, @FIXED .. 68. Converting numbers to currency text: @DOLLAR, @DOLLARTEXT .. 69. Other conversions: @FRACTION, @CHAR .. 70. Converting text to numbers: @VALUE, @DATEVALUE, @TIMEVALUE .. 70. How to convert a number to left-padded text .. 71. How to convert numeric text formatted as #- into usable numbers .. 71. How to convert a number to feet and inches .. 72.
6 How to convert a date number into the day or month as text .. 72. How to convert numeric dates into text dates .. 73. How to convert text dates/times into numeric dates/times .. 73. How to convert decimal time to hours and minutes .. 74. How to convert clock times to seconds .. 74. How to add one hour to a given time .. 74. How to create a chronologically sortable column from text numbers .. 75. Appendix: Custom numeric formats .. 76. 10 Getting information about and from a database 78. Getting the width and height of a block: @COLS, @ROWS, @SHEETS .. 79. Getting column and row numbers for a block: @COLUMN, @ROW .. 79. Column identifiers: @INDEXTOLETTER, @LETTERTOINDEX .. 79. Counting (non-)blank cells: @COUNT, @COUNTBLANK .. 80. Counting conditionally: @COUNTIF .. 80. Basic functions for extracting numeric information from a database .. 81. Retrieval of last entry: @LASTCELLVALUE .. 81. Retrieval of parallel value: @LOOKUP .. 81. Retrieval by index value: @VLOOKUP, @HLOOKUP, @VHLOOKUP.
7 82. Retrieval by column and row: @INDEX, @XINDEX .. 83. Comparing the preceding functions .. 84. Retrieval by text coordinates: @@ .. 84. Determining column/row offset from matches: @MATCH .. 84. Getting coordinates of extreme values: @MAXLOOKUP, @MINLOOOKUP . 86. How (not) to derive coordinates relative to the current cell .. 86. How to retrieve data from cells relative to the current cell .. 89. How to identify the first blank row and cell in a column of data .. 90. How to get the coordinates of a continuous column of entries .. 91. How to count the non-blank cells in the index column of a given block .. 92. How to get the coordinates of a named block .. 92. How to get the value in column that is parallel to the largest value in another column.. 93. How to get every 10th value in a column .. 93. How to get/sum the last five values in a column to which data is added .. 93. How to average the last three numbers, skipping blanks .. 94. How to determine if the cellpointer is in a particular block.
8 95. How to determine if a value is in a column or row .. 96. How to mark duplicates in a column .. 96. How to return the (first) matching row of data in a database .. 97. How to find and return matches after the first one .. 97. How to find the top three scores in a database .. 101. III Quattro Pro Macros 105. 11 Introduction to Quattro Pro Macros 106. Macro commands and text .. 106. Normal sequence of commands .. 108. Making commands conditional: {If} .. 108. Detouring or Breaking: {Cell} and {Branch Cell}, {Quit}, {Return} .. 109. Error handling: {OnError} .. 110. Pausing: {?} and {Wait} .. 110. Repetition (Looping) by branching back .. 111. Repetition for a specific number of loops: {For} .. 111. Speed #1 Display Options: {WindowsOff} and Redraw .. 113. Speed #2 Recalculation Options: Manual, {Calc} and {Recalc} .. 115. Speed #3 Close other notebooks and minimize use of functions .. 116. Automating startup and shutdown Macros .. 116. Naming cells to expedite launching Macros .
9 116. Creating toolbar icons and keystroke shortcuts to launch Macros .. 118. Adding command buttons to launch Macros .. 118. Troubleshooting ambiguous cell references in Macros .. 118. 12 Basic File System Macros 120. Opening and using notebooks: {FileOpen} and {Activate} .. 120. Saving a QP Notebook: {FileSave}, {FileSaveAll}, and {FileSaveAs} .. 121. Closing a QP notebook: {FileClose} and {FileCloseAll} .. 121. 13 Navigation and Selection Macros 122. Cursor Keys: {U}, {D}, {L}, {R}, {Home}, {End}, {PgDn}, {PgUp} .. 122. Selecting Cells/Blocks: {Shift}, {Goto}, {SelectBlock}, {EditGoto}, etc.. 123. How to move to the leftmost cell on the row .. 125. How to move to the first blank cell at the bottom of a column of data .. 125. How to move to the first blank cell at the right of a row of data .. 125. How to move to the same cell on the next page .. 125. How to move to the same cell on the prior page .. 126. How to move to each page successively .. 127.
10 How to round-trip, to move from one cell to another, and then return .. 127. How to select the current and adjoining cells .. 127. How to select the same cells on restarting, after closing the file .. 128. How to select cells in a different notebook .. 128. How to select a cell in a given column based on a row number stored in a particular cell .. 129. How to move to a given value in the index column of a database .. 129. How to test whether the active cell is in a particular sheet .. 130. 14 Interface Macros 131. Sound: {Beep} .. 131. Giving information: {Message} .. 131. How to see where the message will appear .. 132. Making basic choices: {Alert} .. 132. Getting input: {Get}, {GetNumber}, {GetLabel} .. 134. Inhibiting input, avoiding crashes .. 134. Pop-up menus: {Menubranch} .. 135. Displaying information in cells .. 135. How (not) to display a Waiting .. message .. 136. How to display progressing values .. 136. How to display a progress bar .. 137.
