Quattro Pro Formulas, Functions, and Macros
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
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
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 . . . . . . . . . . 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 . . . . . 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
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
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- 027 2009 tips and tricks for creating the reports your
- worksheet powerview
- package textclean r
- technology excel
- quattro pro formulas functions and macros
- basic excel competencies below is a list of basic
- cell contents each cell can contain text or a value or
- advanced excel tips and tricks
- 6 0 element selection tool
- what s what s the excel edit what s the arcgis edit the
Related searches
- excel functions and formulas pdf
- marketing functions and roles
- absolute value functions and translations quizlet
- managerial functions and roles
- manager functions and responsibilities
- ms excel functions and formulas
- management roles functions and skills
- functions and function notation
- excel formulas right and left
- vba and macros pdf
- iphone 11 pro max tips and tricks
- excel formulas lookup and match