Sheet Formula List

Sheet Formula List

All used formulas are put into this section, and referenced when used.

 Data  Description

 B   Always 02 ?

 XListE List of Sheet Formulas

When referenced, the formula number is counted from the tail of the list: the last entry is referenced as 00, the butlast as 02 etc. (X-numbered?).

Sheet Formula

A formula is encoded as an SListB. The formula itself is layed out in memory in Reverse Polish Notation (RPN), and consists of Sheet Formula Elements.

 Formula   Lay-out

 1-5     1 5 -

 1+2×3    1 2 3 × +

 sin(10×3)-4  10 3 × sin 4 -

sum(1,2,3) sum 1 2 3 sum

Sheet Formula Elements

Each formula element is identified by a marker, which is optionally followed by more data (for example, an integer is layed out as the marker 20 followed by the four bytes of a SInt).

There are a few special markers. Each formula ends with marker 15. Markers 2A and 2B are used to separate the arguments of the var-arg operators (see below).

Marker  Data following  Args Description 

01          2 operator < (less than)

02          2 operator <= (less than or equals)

03          2 operator > (greater than)

04          2 operator >= (greater than or equals)

05          2 operator <> (not equals)

06          2 operator = (equals)

07          2 operator + (addition)

08          2 operator - (subtraction)

09          2 operator * (multiplication)

0A          2 operator / (division)

0B          2 operator ^ (power)

0C          1 operator + (positive prefix)

0D          1 operator - (negative prefix)

0E          1 operator NOT

0F          2 operator AND

10          2 operator OR

11          2 operator & (string concatenate)

12          1 brackets () 

13

14

15          0 end-of-formula

16

17

18

19

1A

1B

1C

1D

1E

1F  Float      0 double precision floating point number (8 bytes)

20  SInt       0 signed integer number (4 bytes)

21

22

23

24

25  Sheet Variable Reference 0 named variable

26  BListB       0 text string

27  Sheet Cell Reference   0 single cell reference

28  Sheet Cell Block   0 cell block reference

29  Sheet Cell Block   0 same as 28, but appears within vararg functions

2A          0 operand separator within vararg functions

2B          0 vararg operand end marker

2C 

2D

2E

2F

30

31

32

33          0 FALSE

34          3 IF

35          0 TRUE

36          2 CELL

37          1 ERROR.TYPE

38          1 ISBLANK

39          1 ISERR

3A          1 ISERROR

3B          1 ISLOGICAL

3C          1 ISNA

3D          1 ISNONTEXT

3E          1 ISNUMBER

3F          1 ISTEXT

40          1 N

41          1 TYPE

42          2 ADDRESS

43          1 COLUMN

44          1 COLUMNS

45          3 HLOOKUP

46          3 INDEX

47          1 INDIRECT

48          3 LOOKUP

49          3 OFFSET

4A          1 ROW

4B          1 ROWS

4C          3 VLOOKUP

4D          1 CHAR

4E          1 CODE

4F          2 EXACT

50          3 FIND

51          2 LEFT

52          1 LEN

53          1 LOWER

54          3 MID

55          1 PROPER

56          4 REPLACE

57          2 REPT

58          2 RIGHT

59          2 STRING

5A          1 T

5B          1 TRIM

5C          1 UPPER

5D          1 VALUE

5E          3 DATE

5F          1 DATEVALUE

60          1 DAY

61          1 HOUR

62          1 MINUTE

63          1 MONTH

64          0 NOW

65          1 SECOND

66          0 TODAY

67          3 TIME

68          1 TIMEVALUE

69          1 YEAR

6A          1 ABS

6B          1 ACOS

6C          1 ASIN

6D          1 ATAN

6E          2 ATAN2

6F          1 COS

70          1 DEGREES

71          1 EXP

72          1 FACT

73          1 INT

74          1 LN

75          1 LOG10

76          2 MOD

77          0 PI

78          1 RADIANS

79          0 RAND

7A          2 ROUND

7B          1 SIGN

7C          1 SIN

7D          1 SQRT

7E          2 SUMPRODUCT

7F          1 TAN

80          1 TRUNC

81          3 CTERM

82          4 DDB

83          3 FV

84          2 IRR

85          2 NPV

86          3 PMT

87          3 PV

88          3 RATE

89          3 SLN

8A          4 SYD

8B          3 TERM

8C          2 COMBIN

8D          2 PERMUT

8E  Sheet Formula Varargs  0 AVERAGE

8F  Sheet Formula Varargs  0 CHOOSE

90  Sheet Formula Varargs  0 COUNT

91  Sheet Formula Varargs  0 COUNTA

92  Sheet Formula Varargs  0 COUNTBLANK

93  Sheet Formula Varargs  0 MAX

94  Sheet Formula Varargs  0 MIN

95  Sheet Formula Varargs  0 PRODUCT

96  Sheet Formula Varargs  0 STDEVP

97  Sheet Formula Varargs  0 STDEV

98  Sheet Formula Varargs  0 SUM

99  Sheet Formula Varargs  0 SUMSQ

9A  Sheet Formula Varargs  0 VARP

9B  Sheet Formula Varargs  0 VAR

In the above table, args is the number of elements 'popped' from the stack before the result is 'pushed'. As you can see, funtions with a variable number of arguments are handled specially, and never pop previous values.

Sheet Formula Varargs

Some operators have a variable number of operands. Each operand in its turn consists of Sheet Formula Elements . The encoding is quite different from that used for normal operators. After the operator marker, the operands follow as formulas (without the initial length encoding and without the final 15 marker), separated by the 2A marker. The last operand is followed by the 2B marker, and after this the operator marker is repeated, followed by a word with the number of parameters. Schematically:

 Size  Description

 B   Operator marker

    Operand 1 (Sheet Formula Elements)

 B   2A marker

    Operand 2 (Sheet Formula Elements)

    ....

 B   2A marker

 B   2B marker

 B   Operator marker

 W   Number of operands