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.

Google Online Preview   Download