Home » Archimedes archive » Apps » PipeDream 4 (1991) (Colton Software) (Examples Disc).adf » PD3_users/4warned
PD3_users/4warned
This website contains an archive of files for the Acorn Electron, BBC Micro, Acorn Archimedes, Commodore 16 and Commodore 64 computers, which Dominic Ford has rescued from his private collection of floppy disks and cassettes.
Some of these files were originally commercial releases in the 1980s and 1990s, but they are now widely available online. I assume that copyright over them is no longer being asserted. If you own the copyright and would like files to be removed, please contact me.
| Tape/disk: | Home » Archimedes archive » Apps » PipeDream 4 (1991) (Colton Software) (Examples Disc).adf |
| Filename: | PD3_users/4warned |
| Read OK: | ✔ |
| File size: | 2080 bytes |
| Load address: | FFFDDE43 |
| Exec address: | AA419105 |
Duplicates
There is 1 duplicate copy of this file in the archive:
- Archimedes archive » Zipped Apps » PipeDream » PD3_users/4warned
- Archimedes archive » Apps » PipeDream 4 (1991) (Colton Software) (Examples Disc).adf » PD3_users/4warned
File contents
%OP%VS4.1175 (01-Feb-92), Stuart Keith Swales, R9200 7500 3900 8299
%OP%PL0
%OP%TM4
%OP%BM4
%OP%LM6
%OP%WC2,1278,156,1336,0,0,0,0
%OP%LC0,5
%CO:A,12,72%
%C%%H2%The PipeDream 4 Spreadsheet for PipeDream 3 Users
There are various changes that have been made to the PipeDream 4
spreadsheet that affect PipeDream 3 users who wish to use their
existing spreadsheets.
1. Column and Row recalculation
PipeDream 4 cannot recalculate by row or by column. PipeDream 4 always
recalculates in natural fashion. The Manual/Auto option still exists
and is accessible from the PipeDream 4 icon menu.
PipeDream 3 spreadsheets such as bank statements which had very nested
calculations which gave the "Stack overflow" error message and
necessitated switching to row or column recalculation will calculate
without any problem on PipeDream 4 as long as there is enough memory.
2. Iteration
PipeDream 4 does not have the global iteration options that PipeDream 3
has. Instead you should use the looping facilities in the custom
function language which are much more powerful. As a result the
iteration counter LOOPC has also been removed.
3. Linking files
PipeDream 4 does not have the linking file functions which are
available in PipeDream 3. Instead we have provided suggested custom
functions read and write on the examples disc for you to replace your
linking files. You can use a custom function sheet as a linking file by
writing values into it with the set_value function. This is much faster
and potentially more powerful.
4. Single and Double inverted commas
The PipeDream 4 spreadsheet accepts only double inverted commas (") for
strings. Database function (see below) conditions are no longer
strings, so strings in database function conditions are entered, like
all strings, with double inverted commas. If you have used single
inverted commas in PipeDream 3 files, use the Search and Replace
command to change them to double inverted commas. This is backward
compatible with PipeDream 3 (except in database functions).
5. Index
The index function now requires one extra argument (the first one).
This is a range or array which specifies the area into which you are
indexing. In PipeDream 3 this area was always the whole spreadsheet; in
PipeDream 4, you must specify the area. We recommend that you think
about the area that you pass to index. The smaller the area, the fewer
times index will be recalculated, and the faster your spreadsheets will
be. Typically, index extract a piece of information from a table: the
first parameter to index in PipeDream 4 should be the range of the
table.
6. Database functions
Three changes have been made to database functions in PipeDream 4:
(i)
(ii)
(iii)
7. Intelligent evaluation of IF, OR and AND.
PipeDream 4 avoids evaluating the unnecessary parts of an IF OR or AND.
For example:
In all three cases, the 3+3 will never be evaluated since PipeDream 4
already has enough information to work out the result of the test. This
is very unlikely to adversely affect existing PipeDream 3 spreadsheets
unless you rely on the fact the PipeDream 3 always calculates all the
parts of an IF or OR or AND. It will make many spreadsheets
recalculate more quickly.
8. Undefined names
Since the PipeDream 4 spreadsheet can use names to refer to parts of
the spreadsheet rather than just slot references and ranges, a whole
new set of formulae become valid for PipeDream 4 which PipeDream 3
would have recognised as text slots. This may cause problems if you
have your 'new slot format' set to 'numbers' in the options dialogue
box. For example:
is now a valid expression, and will be entered by PipeDream 4 as a
formula returning 'Name not defined' (assuming you have not defined the
names Start and Finish).
You may need to change the 'new slot format' setting to text or to
enter the text differently: Start to Finish or Start_Finish or
"Start�to�finish".
PipeDream 4 specifically excludes the case of a single undefined name,
so typing Fred into a slot will be treated as text.
A way to prevent PipeDream thinking your expression is valid is to
force in invalid punctuation characters. Try putting in a valid
expression but add to the end a "hard space" by pressing Alt-Space.
Alternatively in the example above you can use a "hard hyphen" by
entering Alt-hyphen.
9. Names
Names are faster than ranges and use up less memory, so it's a good
idea to use named area in your spreadsheets rather than ranges,
especially if the range is replicated over a large area in a
spreadsheet.
10. Background recalculation
You should find the background recalculation in PipeDream 4 much
smoother than PipeDream 3 - when editing a large spreadsheet the
hourglass should hardly ever appear and you should be able to carry on
doing things on your computer while PipeDream 4 recalculates. You may
notice operation being slightly more sluggish whilst background
recalculation is in progress.
11. Slot references to strings
Since PipeDream 4 now has comprehensive string handling, the action of
references to text slots or slots containing strings is rather
different. Suppose:
A1 contains
A2 contains
A3 contains
A4 contains
A5 contains
%H2%Formula
A1
A2
A3
A4
A5
1+A1
1+A2
1+A3
1+A4
1+A5
if(A1="","Blank",if(A1=0,"Zero","Not empty"))
if(A2="","Blank",if(A2=0,"Zero","Not empty"))
if(A3="","Blank",if(A3=0,"Zero","Not empty"))
if(A4="","Blank",if(A4=0,"Zero","Not empty"))
if(A5="","Blank",if(A5=0,"Zero","Not empty"))
If you get "String not expected" errors it's probably because of an
explicit reference to a slot containing a string.
Functions like sum() are not affected since they are defined to ignore
non-numeric slots.
12. Date functions
There are many new date and time functions. One consequence is that
PipeDream 3's function date is now called today in PipeDream 4. The
date function in Pipedream 4 builds a date from year, month and day
components.
Dates can now span more than century and can go back to the year 100.
Dates before the year 100 are treated as dates in the current century.
A consequence of this is that date slots will print two characters
wider than they did in PipeDream 3. The Auto width command may well
sort this out for you.
%CO:B,12,60%
The condition argument is no longer a string. You must
edit the condition argument and remove the (probable
single) quotes from around the condition string.
The condition argument can no longer contain hashes (#)
to indicate no reference update during evaluation.
Dollars ($), like before, indicate, as in all other
references and ranges, that the column or row is to be
fixed when the formula is replicated or copied.
Enter a single slot reference where you don't want the
reference updated during evaluation; enter a range
where the formula is to be updated.
As mentioned above, you must enter a range where you
want the condition updated for each slot in a database
function:
PD3: dsum(a1a100, 'b1>10')
PD4: dsum(a1a100, b1b100>10)
PD3: dsum(a1a100, 'b1="fred"')
PD4: dsum(a1a100, b1b100="fred")
PD3: dsum(a1a100, 'b1=a#200 & c1>10)
PD4: dsum(a1a100, b1b100=a200 & c1c100>10)
PD3: dsum(a1a100, '$b1=$a$#200 & $c1>$a$#201')
PD4: dsum(a1a100, $b1$b100=$a$100 & $c1$c100>$a$201)
if(1,2+2,3+3)
0 & 3+3
2+2 | 3+3 if(0,3+3,2+2)
Start-Finish
nothing (blank cell)
text slot: Hello
number slot: "Hello"
number slot: 0
number slot: " "
%CO:C,12,48%%CO:D,12,36%
or
or
or
%CO:E,12,24%
%H2%PD 3
0
Hello
"Hello"
0
0
1
Hello
"Hello"
1
1
Blank
Not empty
Not empty
Blank
Blank
%CO:F,12,0%
%H2%PD 4
blank
"Hello"
"Hello"
0
blank
1
String not expected
String not expected
1
1
Blank
Not empty
Not empty
Blank
Blank
00000000 25 4f 50 25 56 53 34 2e 31 31 37 35 20 28 30 31 |%OP%VS4.1175 (01|
00000010 2d 46 65 62 2d 39 32 29 2c 20 53 74 75 61 72 74 |-Feb-92), Stuart|
00000020 20 4b 65 69 74 68 20 53 77 61 6c 65 73 2c 20 52 | Keith Swales, R|
00000030 39 32 30 30 20 37 35 30 30 20 33 39 30 30 20 38 |9200 7500 3900 8|
00000040 32 39 39 20 0a 25 4f 50 25 50 4c 30 0a 25 4f 50 |299 .%OP%PL0.%OP|
00000050 25 54 4d 34 0a 25 4f 50 25 42 4d 34 0a 25 4f 50 |%TM4.%OP%BM4.%OP|
00000060 25 4c 4d 36 0a 25 4f 50 25 57 43 32 2c 31 32 37 |%LM6.%OP%WC2,127|
00000070 38 2c 31 35 36 2c 31 33 33 36 2c 30 2c 30 2c 30 |8,156,1336,0,0,0|
00000080 2c 30 0a 25 4f 50 25 4c 43 30 2c 35 0a 25 43 4f |,0.%OP%LC0,5.%CO|
00000090 3a 41 2c 31 32 2c 37 32 25 0a 25 43 25 25 48 32 |:A,12,72%.%C%%H2|
000000a0 25 54 68 65 20 50 69 70 65 44 72 65 61 6d 20 34 |%The PipeDream 4|
000000b0 20 53 70 72 65 61 64 73 68 65 65 74 20 66 6f 72 | Spreadsheet for|
000000c0 20 50 69 70 65 44 72 65 61 6d 20 33 20 55 73 65 | PipeDream 3 Use|
000000d0 72 73 0a 0a 54 68 65 72 65 20 61 72 65 20 76 61 |rs..There are va|
000000e0 72 69 6f 75 73 20 63 68 61 6e 67 65 73 20 74 68 |rious changes th|
000000f0 61 74 20 68 61 76 65 20 62 65 65 6e 20 6d 61 64 |at have been mad|
00000100 65 20 74 6f 20 74 68 65 20 50 69 70 65 44 72 65 |e to the PipeDre|
00000110 61 6d 20 34 20 0a 73 70 72 65 61 64 73 68 65 65 |am 4 .spreadshee|
00000120 74 20 74 68 61 74 20 61 66 66 65 63 74 20 50 69 |t that affect Pi|
00000130 70 65 44 72 65 61 6d 20 33 20 75 73 65 72 73 20 |peDream 3 users |
00000140 77 68 6f 20 77 69 73 68 20 74 6f 20 75 73 65 20 |who wish to use |
00000150 74 68 65 69 72 20 0a 65 78 69 73 74 69 6e 67 20 |their .existing |
00000160 73 70 72 65 61 64 73 68 65 65 74 73 2e 0a 0a 31 |spreadsheets...1|
00000170 2e 20 43 6f 6c 75 6d 6e 20 61 6e 64 20 52 6f 77 |. Column and Row|
00000180 20 72 65 63 61 6c 63 75 6c 61 74 69 6f 6e 0a 0a | recalculation..|
00000190 50 69 70 65 44 72 65 61 6d 20 34 20 63 61 6e 6e |PipeDream 4 cann|
000001a0 6f 74 20 72 65 63 61 6c 63 75 6c 61 74 65 20 62 |ot recalculate b|
000001b0 79 20 72 6f 77 20 6f 72 20 62 79 20 63 6f 6c 75 |y row or by colu|
000001c0 6d 6e 2e 20 50 69 70 65 44 72 65 61 6d 20 34 20 |mn. PipeDream 4 |
000001d0 61 6c 77 61 79 73 20 0a 72 65 63 61 6c 63 75 6c |always .recalcul|
000001e0 61 74 65 73 20 69 6e 20 6e 61 74 75 72 61 6c 20 |ates in natural |
000001f0 66 61 73 68 69 6f 6e 2e 20 54 68 65 20 4d 61 6e |fashion. The Man|
00000200 75 61 6c 2f 41 75 74 6f 20 6f 70 74 69 6f 6e 20 |ual/Auto option |
00000210 73 74 69 6c 6c 20 65 78 69 73 74 73 20 0a 61 6e |still exists .an|
00000220 64 20 69 73 20 61 63 63 65 73 73 69 62 6c 65 20 |d is accessible |
00000230 66 72 6f 6d 20 74 68 65 20 50 69 70 65 44 72 65 |from the PipeDre|
00000240 61 6d 20 34 20 69 63 6f 6e 20 6d 65 6e 75 2e 0a |am 4 icon menu..|
00000250 0a 50 69 70 65 44 72 65 61 6d 20 33 20 73 70 72 |.PipeDream 3 spr|
00000260 65 61 64 73 68 65 65 74 73 20 73 75 63 68 20 61 |eadsheets such a|
00000270 73 20 62 61 6e 6b 20 73 74 61 74 65 6d 65 6e 74 |s bank statement|
00000280 73 20 77 68 69 63 68 20 68 61 64 20 76 65 72 79 |s which had very|
00000290 20 6e 65 73 74 65 64 20 0a 63 61 6c 63 75 6c 61 | nested .calcula|
000002a0 74 69 6f 6e 73 20 77 68 69 63 68 20 67 61 76 65 |tions which gave|
000002b0 20 74 68 65 20 22 53 74 61 63 6b 20 6f 76 65 72 | the "Stack over|
000002c0 66 6c 6f 77 22 20 65 72 72 6f 72 20 6d 65 73 73 |flow" error mess|
000002d0 61 67 65 20 61 6e 64 20 0a 6e 65 63 65 73 73 69 |age and .necessi|
000002e0 74 61 74 65 64 20 73 77 69 74 63 68 69 6e 67 20 |tated switching |
000002f0 74 6f 20 72 6f 77 20 6f 72 20 63 6f 6c 75 6d 6e |to row or column|
00000300 20 72 65 63 61 6c 63 75 6c 61 74 69 6f 6e 20 77 | recalculation w|
00000310 69 6c 6c 20 63 61 6c 63 75 6c 61 74 65 20 0a 77 |ill calculate .w|
00000320 69 74 68 6f 75 74 20 61 6e 79 20 70 72 6f 62 6c |ithout any probl|
00000330 65 6d 20 6f 6e 20 50 69 70 65 44 72 65 61 6d 20 |em on PipeDream |
00000340 34 20 61 73 20 6c 6f 6e 67 20 61 73 20 74 68 65 |4 as long as the|
00000350 72 65 20 69 73 20 65 6e 6f 75 67 68 20 6d 65 6d |re is enough mem|
00000360 6f 72 79 2e 0a 0a 32 2e 20 49 74 65 72 61 74 69 |ory...2. Iterati|
00000370 6f 6e 0a 0a 50 69 70 65 44 72 65 61 6d 20 34 20 |on..PipeDream 4 |
00000380 64 6f 65 73 20 6e 6f 74 20 68 61 76 65 20 74 68 |does not have th|
00000390 65 20 67 6c 6f 62 61 6c 20 69 74 65 72 61 74 69 |e global iterati|
000003a0 6f 6e 20 6f 70 74 69 6f 6e 73 20 74 68 61 74 20 |on options that |
000003b0 50 69 70 65 44 72 65 61 6d 20 33 20 0a 68 61 73 |PipeDream 3 .has|
000003c0 2e 20 49 6e 73 74 65 61 64 20 79 6f 75 20 73 68 |. Instead you sh|
000003d0 6f 75 6c 64 20 75 73 65 20 74 68 65 20 6c 6f 6f |ould use the loo|
000003e0 70 69 6e 67 20 66 61 63 69 6c 69 74 69 65 73 20 |ping facilities |
000003f0 69 6e 20 74 68 65 20 63 75 73 74 6f 6d 20 0a 66 |in the custom .f|
00000400 75 6e 63 74 69 6f 6e 20 6c 61 6e 67 75 61 67 65 |unction language|
00000410 20 77 68 69 63 68 20 61 72 65 20 6d 75 63 68 20 | which are much |
00000420 6d 6f 72 65 20 70 6f 77 65 72 66 75 6c 2e 20 41 |more powerful. A|
00000430 73 20 61 20 72 65 73 75 6c 74 20 74 68 65 20 0a |s a result the .|
00000440 69 74 65 72 61 74 69 6f 6e 20 63 6f 75 6e 74 65 |iteration counte|
00000450 72 20 4c 4f 4f 50 43 20 68 61 73 20 61 6c 73 6f |r LOOPC has also|
00000460 20 62 65 65 6e 20 72 65 6d 6f 76 65 64 2e 0a 0a | been removed...|
00000470 33 2e 20 4c 69 6e 6b 69 6e 67 20 66 69 6c 65 73 |3. Linking files|
00000480 0a 0a 50 69 70 65 44 72 65 61 6d 20 34 20 64 6f |..PipeDream 4 do|
00000490 65 73 20 6e 6f 74 20 68 61 76 65 20 74 68 65 20 |es not have the |
000004a0 6c 69 6e 6b 69 6e 67 20 66 69 6c 65 20 66 75 6e |linking file fun|
000004b0 63 74 69 6f 6e 73 20 77 68 69 63 68 20 61 72 65 |ctions which are|
000004c0 20 0a 61 76 61 69 6c 61 62 6c 65 20 69 6e 20 50 | .available in P|
000004d0 69 70 65 44 72 65 61 6d 20 33 2e 20 49 6e 73 74 |ipeDream 3. Inst|
000004e0 65 61 64 20 77 65 20 68 61 76 65 20 70 72 6f 76 |ead we have prov|
000004f0 69 64 65 64 20 73 75 67 67 65 73 74 65 64 20 63 |ided suggested c|
00000500 75 73 74 6f 6d 20 0a 66 75 6e 63 74 69 6f 6e 73 |ustom .functions|
00000510 20 72 65 61 64 20 61 6e 64 20 77 72 69 74 65 20 | read and write |
00000520 6f 6e 20 74 68 65 20 65 78 61 6d 70 6c 65 73 20 |on the examples |
00000530 64 69 73 63 20 66 6f 72 20 79 6f 75 20 74 6f 20 |disc for you to |
00000540 72 65 70 6c 61 63 65 20 79 6f 75 72 20 0a 6c 69 |replace your .li|
00000550 6e 6b 69 6e 67 20 66 69 6c 65 73 2e 20 59 6f 75 |nking files. You|
00000560 20 63 61 6e 20 75 73 65 20 61 20 63 75 73 74 6f | can use a custo|
00000570 6d 20 66 75 6e 63 74 69 6f 6e 20 73 68 65 65 74 |m function sheet|
00000580 20 61 73 20 61 20 6c 69 6e 6b 69 6e 67 20 66 69 | as a linking fi|
00000590 6c 65 20 62 79 20 0a 77 72 69 74 69 6e 67 20 76 |le by .writing v|
000005a0 61 6c 75 65 73 20 69 6e 74 6f 20 69 74 20 77 69 |alues into it wi|
000005b0 74 68 20 74 68 65 20 73 65 74 5f 76 61 6c 75 65 |th the set_value|
000005c0 20 66 75 6e 63 74 69 6f 6e 2e 20 54 68 69 73 20 | function. This |
000005d0 69 73 20 6d 75 63 68 20 66 61 73 74 65 72 20 0a |is much faster .|
000005e0 61 6e 64 20 70 6f 74 65 6e 74 69 61 6c 6c 79 20 |and potentially |
000005f0 6d 6f 72 65 20 70 6f 77 65 72 66 75 6c 2e 0a 0a |more powerful...|
00000600 34 2e 20 53 69 6e 67 6c 65 20 61 6e 64 20 44 6f |4. Single and Do|
00000610 75 62 6c 65 20 69 6e 76 65 72 74 65 64 20 63 6f |uble inverted co|
00000620 6d 6d 61 73 0a 0a 54 68 65 20 50 69 70 65 44 72 |mmas..The PipeDr|
00000630 65 61 6d 20 34 20 73 70 72 65 61 64 73 68 65 65 |eam 4 spreadshee|
00000640 74 20 61 63 63 65 70 74 73 20 6f 6e 6c 79 20 64 |t accepts only d|
00000650 6f 75 62 6c 65 20 69 6e 76 65 72 74 65 64 20 63 |ouble inverted c|
00000660 6f 6d 6d 61 73 20 28 22 29 20 66 6f 72 20 0a 73 |ommas (") for .s|
00000670 74 72 69 6e 67 73 2e 20 44 61 74 61 62 61 73 65 |trings. Database|
00000680 20 66 75 6e 63 74 69 6f 6e 20 28 73 65 65 20 62 | function (see b|
00000690 65 6c 6f 77 29 20 63 6f 6e 64 69 74 69 6f 6e 73 |elow) conditions|
000006a0 20 61 72 65 20 6e 6f 20 6c 6f 6e 67 65 72 20 0a | are no longer .|
000006b0 73 74 72 69 6e 67 73 2c 20 73 6f 20 73 74 72 69 |strings, so stri|
000006c0 6e 67 73 20 69 6e 20 64 61 74 61 62 61 73 65 20 |ngs in database |
000006d0 66 75 6e 63 74 69 6f 6e 20 63 6f 6e 64 69 74 69 |function conditi|
000006e0 6f 6e 73 20 61 72 65 20 65 6e 74 65 72 65 64 2c |ons are entered,|
000006f0 20 6c 69 6b 65 20 0a 61 6c 6c 20 73 74 72 69 6e | like .all strin|
00000700 67 73 2c 20 77 69 74 68 20 64 6f 75 62 6c 65 20 |gs, with double |
00000710 69 6e 76 65 72 74 65 64 20 63 6f 6d 6d 61 73 2e |inverted commas.|
00000720 20 49 66 20 79 6f 75 20 68 61 76 65 20 75 73 65 | If you have use|
00000730 64 20 73 69 6e 67 6c 65 20 0a 69 6e 76 65 72 74 |d single .invert|
00000740 65 64 20 63 6f 6d 6d 61 73 20 69 6e 20 50 69 70 |ed commas in Pip|
00000750 65 44 72 65 61 6d 20 33 20 66 69 6c 65 73 2c 20 |eDream 3 files, |
00000760 75 73 65 20 74 68 65 20 53 65 61 72 63 68 20 61 |use the Search a|
00000770 6e 64 20 52 65 70 6c 61 63 65 20 0a 63 6f 6d 6d |nd Replace .comm|
00000780 61 6e 64 20 74 6f 20 63 68 61 6e 67 65 20 74 68 |and to change th|
00000790 65 6d 20 74 6f 20 64 6f 75 62 6c 65 20 69 6e 76 |em to double inv|
000007a0 65 72 74 65 64 20 63 6f 6d 6d 61 73 2e 20 54 68 |erted commas. Th|
000007b0 69 73 20 69 73 20 62 61 63 6b 77 61 72 64 20 0a |is is backward .|
000007c0 63 6f 6d 70 61 74 69 62 6c 65 20 77 69 74 68 20 |compatible with |
000007d0 50 69 70 65 44 72 65 61 6d 20 33 20 28 65 78 63 |PipeDream 3 (exc|
000007e0 65 70 74 20 69 6e 20 64 61 74 61 62 61 73 65 20 |ept in database |
000007f0 66 75 6e 63 74 69 6f 6e 73 29 2e 0a 0a 35 2e 20 |functions)...5. |
00000800 49 6e 64 65 78 0a 0a 54 68 65 20 69 6e 64 65 78 |Index..The index|
00000810 20 66 75 6e 63 74 69 6f 6e 20 6e 6f 77 20 72 65 | function now re|
00000820 71 75 69 72 65 73 20 6f 6e 65 20 65 78 74 72 61 |quires one extra|
00000830 20 61 72 67 75 6d 65 6e 74 20 28 74 68 65 20 66 | argument (the f|
00000840 69 72 73 74 20 6f 6e 65 29 2e 20 0a 54 68 69 73 |irst one). .This|
00000850 20 69 73 20 61 20 72 61 6e 67 65 20 6f 72 20 61 | is a range or a|
00000860 72 72 61 79 20 77 68 69 63 68 20 73 70 65 63 69 |rray which speci|
00000870 66 69 65 73 20 74 68 65 20 61 72 65 61 20 69 6e |fies the area in|
00000880 74 6f 20 77 68 69 63 68 20 79 6f 75 20 61 72 65 |to which you are|
00000890 20 0a 69 6e 64 65 78 69 6e 67 2e 20 49 6e 20 50 | .indexing. In P|
000008a0 69 70 65 44 72 65 61 6d 20 33 20 74 68 69 73 20 |ipeDream 3 this |
000008b0 61 72 65 61 20 77 61 73 20 61 6c 77 61 79 73 20 |area was always |
000008c0 74 68 65 20 77 68 6f 6c 65 20 73 70 72 65 61 64 |the whole spread|
000008d0 73 68 65 65 74 3b 20 69 6e 20 0a 50 69 70 65 44 |sheet; in .PipeD|
000008e0 72 65 61 6d 20 34 2c 20 79 6f 75 20 6d 75 73 74 |ream 4, you must|
000008f0 20 73 70 65 63 69 66 79 20 74 68 65 20 61 72 65 | specify the are|
00000900 61 2e 20 57 65 20 72 65 63 6f 6d 6d 65 6e 64 20 |a. We recommend |
00000910 74 68 61 74 20 79 6f 75 20 74 68 69 6e 6b 20 0a |that you think .|
00000920 61 62 6f 75 74 20 74 68 65 20 61 72 65 61 20 74 |about the area t|
00000930 68 61 74 20 79 6f 75 20 70 61 73 73 20 74 6f 20 |hat you pass to |
00000940 69 6e 64 65 78 2e 20 54 68 65 20 73 6d 61 6c 6c |index. The small|
00000950 65 72 20 74 68 65 20 61 72 65 61 2c 20 74 68 65 |er the area, the|
00000960 20 66 65 77 65 72 20 0a 74 69 6d 65 73 20 69 6e | fewer .times in|
00000970 64 65 78 20 77 69 6c 6c 20 62 65 20 72 65 63 61 |dex will be reca|
00000980 6c 63 75 6c 61 74 65 64 2c 20 61 6e 64 20 74 68 |lculated, and th|
00000990 65 20 66 61 73 74 65 72 20 79 6f 75 72 20 73 70 |e faster your sp|
000009a0 72 65 61 64 73 68 65 65 74 73 20 77 69 6c 6c 20 |readsheets will |
000009b0 0a 62 65 2e 20 20 54 79 70 69 63 61 6c 6c 79 2c |.be. Typically,|
000009c0 20 69 6e 64 65 78 20 65 78 74 72 61 63 74 20 61 | index extract a|
000009d0 20 70 69 65 63 65 20 6f 66 20 69 6e 66 6f 72 6d | piece of inform|
000009e0 61 74 69 6f 6e 20 66 72 6f 6d 20 61 20 74 61 62 |ation from a tab|
000009f0 6c 65 3a 20 74 68 65 20 0a 66 69 72 73 74 20 70 |le: the .first p|
00000a00 61 72 61 6d 65 74 65 72 20 74 6f 20 69 6e 64 65 |arameter to inde|
00000a10 78 20 69 6e 20 50 69 70 65 44 72 65 61 6d 20 34 |x in PipeDream 4|
00000a20 20 73 68 6f 75 6c 64 20 62 65 20 74 68 65 20 72 | should be the r|
00000a30 61 6e 67 65 20 6f 66 20 74 68 65 20 0a 74 61 62 |ange of the .tab|
00000a40 6c 65 2e 0a 0a 36 2e 20 44 61 74 61 62 61 73 65 |le...6. Database|
00000a50 20 66 75 6e 63 74 69 6f 6e 73 0a 0a 54 68 72 65 | functions..Thre|
00000a60 65 20 63 68 61 6e 67 65 73 20 68 61 76 65 20 62 |e changes have b|
00000a70 65 65 6e 20 6d 61 64 65 20 74 6f 20 64 61 74 61 |een made to data|
00000a80 62 61 73 65 20 66 75 6e 63 74 69 6f 6e 73 20 69 |base functions i|
00000a90 6e 20 50 69 70 65 44 72 65 61 6d 20 34 3a 0a 0a |n PipeDream 4:..|
00000aa0 28 69 29 0a 0a 0a 0a 28 69 69 29 0a 0a 0a 0a 0a |(i)....(ii).....|
00000ab0 0a 0a 0a 0a 0a 28 69 69 69 29 0a 0a 0a 0a 0a 0a |.....(iii)......|
00000ac0 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 37 2e 20 49 6e |...........7. In|
00000ad0 74 65 6c 6c 69 67 65 6e 74 20 65 76 61 6c 75 61 |telligent evalua|
00000ae0 74 69 6f 6e 20 6f 66 20 49 46 2c 20 4f 52 20 61 |tion of IF, OR a|
00000af0 6e 64 20 41 4e 44 2e 0a 0a 50 69 70 65 44 72 65 |nd AND...PipeDre|
00000b00 61 6d 20 34 20 61 76 6f 69 64 73 20 65 76 61 6c |am 4 avoids eval|
00000b10 75 61 74 69 6e 67 20 74 68 65 20 75 6e 6e 65 63 |uating the unnec|
00000b20 65 73 73 61 72 79 20 70 61 72 74 73 20 6f 66 20 |essary parts of |
00000b30 61 6e 20 49 46 20 4f 52 20 6f 72 20 41 4e 44 2e |an IF OR or AND.|
00000b40 20 0a 46 6f 72 20 65 78 61 6d 70 6c 65 3a 0a 0a | .For example:..|
00000b50 0a 0a 0a 0a 49 6e 20 61 6c 6c 20 74 68 72 65 65 |....In all three|
00000b60 20 63 61 73 65 73 2c 20 74 68 65 20 33 2b 33 20 | cases, the 3+3 |
00000b70 77 69 6c 6c 20 6e 65 76 65 72 20 62 65 20 65 76 |will never be ev|
00000b80 61 6c 75 61 74 65 64 20 73 69 6e 63 65 20 50 69 |aluated since Pi|
00000b90 70 65 44 72 65 61 6d 20 34 20 0a 61 6c 72 65 61 |peDream 4 .alrea|
00000ba0 64 79 20 68 61 73 20 65 6e 6f 75 67 68 20 69 6e |dy has enough in|
00000bb0 66 6f 72 6d 61 74 69 6f 6e 20 74 6f 20 77 6f 72 |formation to wor|
00000bc0 6b 20 6f 75 74 20 74 68 65 20 72 65 73 75 6c 74 |k out the result|
00000bd0 20 6f 66 20 74 68 65 20 74 65 73 74 2e 20 54 68 | of the test. Th|
00000be0 69 73 20 0a 69 73 20 76 65 72 79 20 75 6e 6c 69 |is .is very unli|
00000bf0 6b 65 6c 79 20 74 6f 20 61 64 76 65 72 73 65 6c |kely to adversel|
00000c00 79 20 61 66 66 65 63 74 20 65 78 69 73 74 69 6e |y affect existin|
00000c10 67 20 50 69 70 65 44 72 65 61 6d 20 33 20 73 70 |g PipeDream 3 sp|
00000c20 72 65 61 64 73 68 65 65 74 73 20 0a 75 6e 6c 65 |readsheets .unle|
00000c30 73 73 20 79 6f 75 20 72 65 6c 79 20 6f 6e 20 74 |ss you rely on t|
00000c40 68 65 20 66 61 63 74 20 74 68 65 20 50 69 70 65 |he fact the Pipe|
00000c50 44 72 65 61 6d 20 33 20 61 6c 77 61 79 73 20 63 |Dream 3 always c|
00000c60 61 6c 63 75 6c 61 74 65 73 20 61 6c 6c 20 74 68 |alculates all th|
00000c70 65 20 0a 70 61 72 74 73 20 6f 66 20 61 6e 20 49 |e .parts of an I|
00000c80 46 20 6f 72 20 4f 52 20 6f 72 20 41 4e 44 2e 20 |F or OR or AND. |
00000c90 20 49 74 20 77 69 6c 6c 20 6d 61 6b 65 20 6d 61 | It will make ma|
00000ca0 6e 79 20 73 70 72 65 61 64 73 68 65 65 74 73 20 |ny spreadsheets |
00000cb0 0a 72 65 63 61 6c 63 75 6c 61 74 65 20 6d 6f 72 |.recalculate mor|
00000cc0 65 20 71 75 69 63 6b 6c 79 2e 0a 0a 38 2e 20 55 |e quickly...8. U|
00000cd0 6e 64 65 66 69 6e 65 64 20 6e 61 6d 65 73 0a 0a |ndefined names..|
00000ce0 53 69 6e 63 65 20 74 68 65 20 50 69 70 65 44 72 |Since the PipeDr|
00000cf0 65 61 6d 20 34 20 73 70 72 65 61 64 73 68 65 65 |eam 4 spreadshee|
00000d00 74 20 63 61 6e 20 75 73 65 20 6e 61 6d 65 73 20 |t can use names |
00000d10 74 6f 20 72 65 66 65 72 20 74 6f 20 70 61 72 74 |to refer to part|
00000d20 73 20 6f 66 20 0a 74 68 65 20 73 70 72 65 61 64 |s of .the spread|
00000d30 73 68 65 65 74 20 72 61 74 68 65 72 20 74 68 61 |sheet rather tha|
00000d40 6e 20 6a 75 73 74 20 73 6c 6f 74 20 72 65 66 65 |n just slot refe|
00000d50 72 65 6e 63 65 73 20 61 6e 64 20 72 61 6e 67 65 |rences and range|
00000d60 73 2c 20 61 20 77 68 6f 6c 65 20 0a 6e 65 77 20 |s, a whole .new |
00000d70 73 65 74 20 6f 66 20 66 6f 72 6d 75 6c 61 65 20 |set of formulae |
00000d80 62 65 63 6f 6d 65 20 76 61 6c 69 64 20 66 6f 72 |become valid for|
00000d90 20 50 69 70 65 44 72 65 61 6d 20 34 20 77 68 69 | PipeDream 4 whi|
00000da0 63 68 20 50 69 70 65 44 72 65 61 6d 20 33 20 0a |ch PipeDream 3 .|
00000db0 77 6f 75 6c 64 20 68 61 76 65 20 72 65 63 6f 67 |would have recog|
00000dc0 6e 69 73 65 64 20 61 73 20 74 65 78 74 20 73 6c |nised as text sl|
00000dd0 6f 74 73 2e 20 54 68 69 73 20 6d 61 79 20 63 61 |ots. This may ca|
00000de0 75 73 65 20 70 72 6f 62 6c 65 6d 73 20 69 66 20 |use problems if |
00000df0 79 6f 75 20 0a 68 61 76 65 20 79 6f 75 72 20 27 |you .have your '|
00000e00 6e 65 77 20 73 6c 6f 74 20 66 6f 72 6d 61 74 27 |new slot format'|
00000e10 20 73 65 74 20 74 6f 20 27 6e 75 6d 62 65 72 73 | set to 'numbers|
00000e20 27 20 69 6e 20 74 68 65 20 6f 70 74 69 6f 6e 73 |' in the options|
00000e30 20 64 69 61 6c 6f 67 75 65 20 0a 62 6f 78 2e 20 | dialogue .box. |
00000e40 46 6f 72 20 65 78 61 6d 70 6c 65 3a 0a 0a 0a 0a |For example:....|
00000e50 69 73 20 6e 6f 77 20 61 20 76 61 6c 69 64 20 65 |is now a valid e|
00000e60 78 70 72 65 73 73 69 6f 6e 2c 20 61 6e 64 20 77 |xpression, and w|
00000e70 69 6c 6c 20 62 65 20 65 6e 74 65 72 65 64 20 62 |ill be entered b|
00000e80 79 20 50 69 70 65 44 72 65 61 6d 20 34 20 61 73 |y PipeDream 4 as|
00000e90 20 61 20 0a 66 6f 72 6d 75 6c 61 20 72 65 74 75 | a .formula retu|
00000ea0 72 6e 69 6e 67 20 27 4e 61 6d 65 20 6e 6f 74 20 |rning 'Name not |
00000eb0 64 65 66 69 6e 65 64 27 20 28 61 73 73 75 6d 69 |defined' (assumi|
00000ec0 6e 67 20 79 6f 75 20 68 61 76 65 20 6e 6f 74 20 |ng you have not |
00000ed0 64 65 66 69 6e 65 64 20 74 68 65 20 0a 6e 61 6d |defined the .nam|
00000ee0 65 73 20 53 74 61 72 74 20 61 6e 64 20 46 69 6e |es Start and Fin|
00000ef0 69 73 68 29 2e 0a 0a 59 6f 75 20 6d 61 79 20 6e |ish)...You may n|
00000f00 65 65 64 20 74 6f 20 63 68 61 6e 67 65 20 74 68 |eed to change th|
00000f10 65 20 27 6e 65 77 20 73 6c 6f 74 20 66 6f 72 6d |e 'new slot form|
00000f20 61 74 27 20 73 65 74 74 69 6e 67 20 74 6f 20 74 |at' setting to t|
00000f30 65 78 74 20 6f 72 20 74 6f 20 0a 65 6e 74 65 72 |ext or to .enter|
00000f40 20 74 68 65 20 74 65 78 74 20 64 69 66 66 65 72 | the text differ|
00000f50 65 6e 74 6c 79 3a 20 53 74 61 72 74 20 74 6f 20 |ently: Start to |
00000f60 46 69 6e 69 73 68 20 6f 72 20 53 74 61 72 74 5f |Finish or Start_|
00000f70 46 69 6e 69 73 68 20 6f 72 20 0a 22 53 74 61 72 |Finish or ."Star|
00000f80 74 a0 74 6f a0 66 69 6e 69 73 68 22 2e 0a 0a 50 |t.to.finish"...P|
00000f90 69 70 65 44 72 65 61 6d 20 34 20 73 70 65 63 69 |ipeDream 4 speci|
00000fa0 66 69 63 61 6c 6c 79 20 65 78 63 6c 75 64 65 73 |fically excludes|
00000fb0 20 74 68 65 20 63 61 73 65 20 6f 66 20 61 20 73 | the case of a s|
00000fc0 69 6e 67 6c 65 20 75 6e 64 65 66 69 6e 65 64 20 |ingle undefined |
00000fd0 6e 61 6d 65 2c 20 0a 73 6f 20 74 79 70 69 6e 67 |name, .so typing|
00000fe0 20 46 72 65 64 20 69 6e 74 6f 20 61 20 73 6c 6f | Fred into a slo|
00000ff0 74 20 77 69 6c 6c 20 62 65 20 74 72 65 61 74 65 |t will be treate|
00001000 64 20 61 73 20 74 65 78 74 2e 0a 0a 41 20 77 61 |d as text...A wa|
00001010 79 20 74 6f 20 70 72 65 76 65 6e 74 20 50 69 70 |y to prevent Pip|
00001020 65 44 72 65 61 6d 20 74 68 69 6e 6b 69 6e 67 20 |eDream thinking |
00001030 79 6f 75 72 20 65 78 70 72 65 73 73 69 6f 6e 20 |your expression |
00001040 69 73 20 76 61 6c 69 64 20 69 73 20 74 6f 20 0a |is valid is to .|
00001050 66 6f 72 63 65 20 69 6e 20 69 6e 76 61 6c 69 64 |force in invalid|
00001060 20 70 75 6e 63 74 75 61 74 69 6f 6e 20 63 68 61 | punctuation cha|
00001070 72 61 63 74 65 72 73 2e 20 20 54 72 79 20 70 75 |racters. Try pu|
00001080 74 74 69 6e 67 20 69 6e 20 61 20 76 61 6c 69 64 |tting in a valid|
00001090 20 0a 65 78 70 72 65 73 73 69 6f 6e 20 62 75 74 | .expression but|
000010a0 20 61 64 64 20 74 6f 20 74 68 65 20 65 6e 64 20 | add to the end |
000010b0 61 20 22 68 61 72 64 20 73 70 61 63 65 22 20 62 |a "hard space" b|
000010c0 79 20 70 72 65 73 73 69 6e 67 20 41 6c 74 2d 53 |y pressing Alt-S|
000010d0 70 61 63 65 2e 20 0a 41 6c 74 65 72 6e 61 74 69 |pace. .Alternati|
000010e0 76 65 6c 79 20 69 6e 20 74 68 65 20 65 78 61 6d |vely in the exam|
000010f0 70 6c 65 20 61 62 6f 76 65 20 79 6f 75 20 63 61 |ple above you ca|
00001100 6e 20 75 73 65 20 61 20 22 68 61 72 64 20 68 79 |n use a "hard hy|
00001110 70 68 65 6e 22 20 62 79 20 0a 65 6e 74 65 72 69 |phen" by .enteri|
00001120 6e 67 20 41 6c 74 2d 68 79 70 68 65 6e 2e 0a 0a |ng Alt-hyphen...|
00001130 0a 39 2e 20 4e 61 6d 65 73 0a 0a 4e 61 6d 65 73 |.9. Names..Names|
00001140 20 61 72 65 20 66 61 73 74 65 72 20 74 68 61 6e | are faster than|
00001150 20 72 61 6e 67 65 73 20 61 6e 64 20 75 73 65 20 | ranges and use |
00001160 75 70 20 6c 65 73 73 20 6d 65 6d 6f 72 79 2c 20 |up less memory, |
00001170 73 6f 20 69 74 27 73 20 61 20 67 6f 6f 64 20 0a |so it's a good .|
00001180 69 64 65 61 20 74 6f 20 75 73 65 20 6e 61 6d 65 |idea to use name|
00001190 64 20 61 72 65 61 20 69 6e 20 79 6f 75 72 20 73 |d area in your s|
000011a0 70 72 65 61 64 73 68 65 65 74 73 20 72 61 74 68 |preadsheets rath|
000011b0 65 72 20 74 68 61 6e 20 72 61 6e 67 65 73 2c 20 |er than ranges, |
000011c0 0a 65 73 70 65 63 69 61 6c 6c 79 20 69 66 20 74 |.especially if t|
000011d0 68 65 20 72 61 6e 67 65 20 69 73 20 72 65 70 6c |he range is repl|
000011e0 69 63 61 74 65 64 20 6f 76 65 72 20 61 20 6c 61 |icated over a la|
000011f0 72 67 65 20 61 72 65 61 20 69 6e 20 61 20 0a 73 |rge area in a .s|
00001200 70 72 65 61 64 73 68 65 65 74 2e 0a 0a 31 30 2e |preadsheet...10.|
00001210 20 42 61 63 6b 67 72 6f 75 6e 64 20 72 65 63 61 | Background reca|
00001220 6c 63 75 6c 61 74 69 6f 6e 0a 0a 59 6f 75 20 73 |lculation..You s|
00001230 68 6f 75 6c 64 20 66 69 6e 64 20 74 68 65 20 62 |hould find the b|
00001240 61 63 6b 67 72 6f 75 6e 64 20 72 65 63 61 6c 63 |ackground recalc|
00001250 75 6c 61 74 69 6f 6e 20 69 6e 20 50 69 70 65 44 |ulation in PipeD|
00001260 72 65 61 6d 20 34 20 6d 75 63 68 20 0a 73 6d 6f |ream 4 much .smo|
00001270 6f 74 68 65 72 20 74 68 61 6e 20 50 69 70 65 44 |other than PipeD|
00001280 72 65 61 6d 20 33 20 2d 20 77 68 65 6e 20 65 64 |ream 3 - when ed|
00001290 69 74 69 6e 67 20 61 20 6c 61 72 67 65 20 73 70 |iting a large sp|
000012a0 72 65 61 64 73 68 65 65 74 20 74 68 65 20 0a 68 |readsheet the .h|
000012b0 6f 75 72 67 6c 61 73 73 20 73 68 6f 75 6c 64 20 |ourglass should |
000012c0 68 61 72 64 6c 79 20 65 76 65 72 20 61 70 70 65 |hardly ever appe|
000012d0 61 72 20 61 6e 64 20 79 6f 75 20 73 68 6f 75 6c |ar and you shoul|
000012e0 64 20 62 65 20 61 62 6c 65 20 74 6f 20 63 61 72 |d be able to car|
000012f0 72 79 20 6f 6e 20 0a 64 6f 69 6e 67 20 74 68 69 |ry on .doing thi|
00001300 6e 67 73 20 6f 6e 20 79 6f 75 72 20 63 6f 6d 70 |ngs on your comp|
00001310 75 74 65 72 20 77 68 69 6c 65 20 50 69 70 65 44 |uter while PipeD|
00001320 72 65 61 6d 20 34 20 72 65 63 61 6c 63 75 6c 61 |ream 4 recalcula|
00001330 74 65 73 2e 20 59 6f 75 20 6d 61 79 20 0a 6e 6f |tes. You may .no|
00001340 74 69 63 65 20 6f 70 65 72 61 74 69 6f 6e 20 62 |tice operation b|
00001350 65 69 6e 67 20 73 6c 69 67 68 74 6c 79 20 6d 6f |eing slightly mo|
00001360 72 65 20 73 6c 75 67 67 69 73 68 20 77 68 69 6c |re sluggish whil|
00001370 73 74 20 62 61 63 6b 67 72 6f 75 6e 64 20 0a 72 |st background .r|
00001380 65 63 61 6c 63 75 6c 61 74 69 6f 6e 20 69 73 20 |ecalculation is |
00001390 69 6e 20 70 72 6f 67 72 65 73 73 2e 0a 0a 31 31 |in progress...11|
000013a0 2e 20 53 6c 6f 74 20 72 65 66 65 72 65 6e 63 65 |. Slot reference|
000013b0 73 20 74 6f 20 73 74 72 69 6e 67 73 0a 0a 53 69 |s to strings..Si|
000013c0 6e 63 65 20 50 69 70 65 44 72 65 61 6d 20 34 20 |nce PipeDream 4 |
000013d0 6e 6f 77 20 68 61 73 20 63 6f 6d 70 72 65 68 65 |now has comprehe|
000013e0 6e 73 69 76 65 20 73 74 72 69 6e 67 20 68 61 6e |nsive string han|
000013f0 64 6c 69 6e 67 2c 20 74 68 65 20 61 63 74 69 6f |dling, the actio|
00001400 6e 20 6f 66 20 0a 72 65 66 65 72 65 6e 63 65 73 |n of .references|
00001410 20 74 6f 20 74 65 78 74 20 73 6c 6f 74 73 20 6f | to text slots o|
00001420 72 20 73 6c 6f 74 73 20 63 6f 6e 74 61 69 6e 69 |r slots containi|
00001430 6e 67 20 73 74 72 69 6e 67 73 20 69 73 20 72 61 |ng strings is ra|
00001440 74 68 65 72 20 0a 64 69 66 66 65 72 65 6e 74 2e |ther .different.|
00001450 20 53 75 70 70 6f 73 65 3a 0a 0a 41 31 20 63 6f | Suppose:..A1 co|
00001460 6e 74 61 69 6e 73 0a 41 32 20 63 6f 6e 74 61 69 |ntains.A2 contai|
00001470 6e 73 0a 41 33 20 63 6f 6e 74 61 69 6e 73 0a 41 |ns.A3 contains.A|
00001480 34 20 63 6f 6e 74 61 69 6e 73 0a 41 35 20 63 6f |4 contains.A5 co|
00001490 6e 74 61 69 6e 73 0a 0a 25 48 32 25 46 6f 72 6d |ntains..%H2%Form|
000014a0 75 6c 61 0a 0a 41 31 0a 41 32 0a 41 33 0a 41 34 |ula..A1.A2.A3.A4|
000014b0 0a 41 35 0a 0a 31 2b 41 31 0a 31 2b 41 32 0a 31 |.A5..1+A1.1+A2.1|
000014c0 2b 41 33 0a 31 2b 41 34 0a 31 2b 41 35 0a 0a 69 |+A3.1+A4.1+A5..i|
000014d0 66 28 41 31 3d 22 22 2c 22 42 6c 61 6e 6b 22 2c |f(A1="","Blank",|
000014e0 69 66 28 41 31 3d 30 2c 22 5a 65 72 6f 22 2c 22 |if(A1=0,"Zero","|
000014f0 4e 6f 74 20 65 6d 70 74 79 22 29 29 0a 69 66 28 |Not empty")).if(|
00001500 41 32 3d 22 22 2c 22 42 6c 61 6e 6b 22 2c 69 66 |A2="","Blank",if|
00001510 28 41 32 3d 30 2c 22 5a 65 72 6f 22 2c 22 4e 6f |(A2=0,"Zero","No|
00001520 74 20 65 6d 70 74 79 22 29 29 0a 69 66 28 41 33 |t empty")).if(A3|
00001530 3d 22 22 2c 22 42 6c 61 6e 6b 22 2c 69 66 28 41 |="","Blank",if(A|
00001540 33 3d 30 2c 22 5a 65 72 6f 22 2c 22 4e 6f 74 20 |3=0,"Zero","Not |
00001550 65 6d 70 74 79 22 29 29 0a 69 66 28 41 34 3d 22 |empty")).if(A4="|
00001560 22 2c 22 42 6c 61 6e 6b 22 2c 69 66 28 41 34 3d |","Blank",if(A4=|
00001570 30 2c 22 5a 65 72 6f 22 2c 22 4e 6f 74 20 65 6d |0,"Zero","Not em|
00001580 70 74 79 22 29 29 0a 69 66 28 41 35 3d 22 22 2c |pty")).if(A5="",|
00001590 22 42 6c 61 6e 6b 22 2c 69 66 28 41 35 3d 30 2c |"Blank",if(A5=0,|
000015a0 22 5a 65 72 6f 22 2c 22 4e 6f 74 20 65 6d 70 74 |"Zero","Not empt|
000015b0 79 22 29 29 0a 0a 49 66 20 79 6f 75 20 67 65 74 |y"))..If you get|
000015c0 20 22 53 74 72 69 6e 67 20 6e 6f 74 20 65 78 70 | "String not exp|
000015d0 65 63 74 65 64 22 20 65 72 72 6f 72 73 20 69 74 |ected" errors it|
000015e0 27 73 20 70 72 6f 62 61 62 6c 79 20 62 65 63 61 |'s probably beca|
000015f0 75 73 65 20 6f 66 20 61 6e 20 0a 65 78 70 6c 69 |use of an .expli|
00001600 63 69 74 20 72 65 66 65 72 65 6e 63 65 20 74 6f |cit reference to|
00001610 20 61 20 73 6c 6f 74 20 63 6f 6e 74 61 69 6e 69 | a slot containi|
00001620 6e 67 20 61 20 73 74 72 69 6e 67 2e 0a 0a 46 75 |ng a string...Fu|
00001630 6e 63 74 69 6f 6e 73 20 6c 69 6b 65 20 73 75 6d |nctions like sum|
00001640 28 29 20 61 72 65 20 6e 6f 74 20 61 66 66 65 63 |() are not affec|
00001650 74 65 64 20 73 69 6e 63 65 20 74 68 65 79 20 61 |ted since they a|
00001660 72 65 20 64 65 66 69 6e 65 64 20 74 6f 20 69 67 |re defined to ig|
00001670 6e 6f 72 65 20 0a 6e 6f 6e 2d 6e 75 6d 65 72 69 |nore .non-numeri|
00001680 63 20 73 6c 6f 74 73 2e 0a 0a 0a 31 32 2e 20 44 |c slots....12. D|
00001690 61 74 65 20 66 75 6e 63 74 69 6f 6e 73 0a 0a 54 |ate functions..T|
000016a0 68 65 72 65 20 61 72 65 20 6d 61 6e 79 20 6e 65 |here are many ne|
000016b0 77 20 64 61 74 65 20 61 6e 64 20 74 69 6d 65 20 |w date and time |
000016c0 66 75 6e 63 74 69 6f 6e 73 2e 20 20 4f 6e 65 20 |functions. One |
000016d0 63 6f 6e 73 65 71 75 65 6e 63 65 20 69 73 20 74 |consequence is t|
000016e0 68 61 74 20 0a 50 69 70 65 44 72 65 61 6d 20 33 |hat .PipeDream 3|
000016f0 27 73 20 66 75 6e 63 74 69 6f 6e 20 64 61 74 65 |'s function date|
00001700 20 69 73 20 6e 6f 77 20 63 61 6c 6c 65 64 20 74 | is now called t|
00001710 6f 64 61 79 20 69 6e 20 50 69 70 65 44 72 65 61 |oday in PipeDrea|
00001720 6d 20 34 2e 20 20 54 68 65 20 0a 64 61 74 65 20 |m 4. The .date |
00001730 66 75 6e 63 74 69 6f 6e 20 69 6e 20 50 69 70 65 |function in Pipe|
00001740 64 72 65 61 6d 20 34 20 62 75 69 6c 64 73 20 61 |dream 4 builds a|
00001750 20 64 61 74 65 20 66 72 6f 6d 20 79 65 61 72 2c | date from year,|
00001760 20 6d 6f 6e 74 68 20 61 6e 64 20 64 61 79 20 0a | month and day .|
00001770 63 6f 6d 70 6f 6e 65 6e 74 73 2e 0a 0a 44 61 74 |components...Dat|
00001780 65 73 20 63 61 6e 20 6e 6f 77 20 73 70 61 6e 20 |es can now span |
00001790 6d 6f 72 65 20 74 68 61 6e 20 63 65 6e 74 75 72 |more than centur|
000017a0 79 20 61 6e 64 20 63 61 6e 20 67 6f 20 62 61 63 |y and can go bac|
000017b0 6b 20 74 6f 20 74 68 65 20 79 65 61 72 20 31 30 |k to the year 10|
000017c0 30 2e 20 20 0a 44 61 74 65 73 20 62 65 66 6f 72 |0. .Dates befor|
000017d0 65 20 74 68 65 20 79 65 61 72 20 31 30 30 20 61 |e the year 100 a|
000017e0 72 65 20 74 72 65 61 74 65 64 20 61 73 20 64 61 |re treated as da|
000017f0 74 65 73 20 69 6e 20 74 68 65 20 63 75 72 72 65 |tes in the curre|
00001800 6e 74 20 63 65 6e 74 75 72 79 2e 20 20 0a 41 20 |nt century. .A |
00001810 63 6f 6e 73 65 71 75 65 6e 63 65 20 6f 66 20 74 |consequence of t|
00001820 68 69 73 20 69 73 20 74 68 61 74 20 64 61 74 65 |his is that date|
00001830 20 73 6c 6f 74 73 20 77 69 6c 6c 20 70 72 69 6e | slots will prin|
00001840 74 20 74 77 6f 20 63 68 61 72 61 63 74 65 72 73 |t two characters|
00001850 20 0a 77 69 64 65 72 20 74 68 61 6e 20 74 68 65 | .wider than the|
00001860 79 20 64 69 64 20 69 6e 20 50 69 70 65 44 72 65 |y did in PipeDre|
00001870 61 6d 20 33 2e 20 20 54 68 65 20 41 75 74 6f 20 |am 3. The Auto |
00001880 77 69 64 74 68 20 63 6f 6d 6d 61 6e 64 20 6d 61 |width command ma|
00001890 79 20 77 65 6c 6c 20 0a 73 6f 72 74 20 74 68 69 |y well .sort thi|
000018a0 73 20 6f 75 74 20 66 6f 72 20 79 6f 75 2e 0a 25 |s out for you..%|
000018b0 43 4f 3a 42 2c 31 32 2c 36 30 25 0a 0a 0a 0a 0a |CO:B,12,60%.....|
000018c0 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a |................|
*
000018f0 0a 0a 0a 0a 0a 0a 0a 54 68 65 20 63 6f 6e 64 69 |.......The condi|
00001900 74 69 6f 6e 20 61 72 67 75 6d 65 6e 74 20 69 73 |tion argument is|
00001910 20 6e 6f 20 6c 6f 6e 67 65 72 20 61 20 73 74 72 | no longer a str|
00001920 69 6e 67 2e 20 59 6f 75 20 6d 75 73 74 20 0a 65 |ing. You must .e|
00001930 64 69 74 20 74 68 65 20 63 6f 6e 64 69 74 69 6f |dit the conditio|
00001940 6e 20 61 72 67 75 6d 65 6e 74 20 61 6e 64 20 72 |n argument and r|
00001950 65 6d 6f 76 65 20 74 68 65 20 28 70 72 6f 62 61 |emove the (proba|
00001960 62 6c 65 20 0a 73 69 6e 67 6c 65 29 20 71 75 6f |ble .single) quo|
00001970 74 65 73 20 66 72 6f 6d 20 61 72 6f 75 6e 64 20 |tes from around |
00001980 74 68 65 20 63 6f 6e 64 69 74 69 6f 6e 20 73 74 |the condition st|
00001990 72 69 6e 67 2e 0a 0a 54 68 65 20 63 6f 6e 64 69 |ring...The condi|
000019a0 74 69 6f 6e 20 61 72 67 75 6d 65 6e 74 20 63 61 |tion argument ca|
000019b0 6e 20 6e 6f 20 6c 6f 6e 67 65 72 20 63 6f 6e 74 |n no longer cont|
000019c0 61 69 6e 20 68 61 73 68 65 73 20 28 23 29 20 0a |ain hashes (#) .|
000019d0 74 6f 20 69 6e 64 69 63 61 74 65 20 6e 6f 20 72 |to indicate no r|
000019e0 65 66 65 72 65 6e 63 65 20 75 70 64 61 74 65 20 |eference update |
000019f0 64 75 72 69 6e 67 20 65 76 61 6c 75 61 74 69 6f |during evaluatio|
00001a00 6e 2e 20 0a 44 6f 6c 6c 61 72 73 20 28 24 29 2c |n. .Dollars ($),|
00001a10 20 6c 69 6b 65 20 62 65 66 6f 72 65 2c 20 69 6e | like before, in|
00001a20 64 69 63 61 74 65 2c 20 61 73 20 69 6e 20 61 6c |dicate, as in al|
00001a30 6c 20 6f 74 68 65 72 20 0a 72 65 66 65 72 65 6e |l other .referen|
00001a40 63 65 73 20 61 6e 64 20 72 61 6e 67 65 73 2c 20 |ces and ranges, |
00001a50 74 68 61 74 20 74 68 65 20 63 6f 6c 75 6d 6e 20 |that the column |
00001a60 6f 72 20 72 6f 77 20 69 73 20 74 6f 20 62 65 20 |or row is to be |
00001a70 0a 66 69 78 65 64 20 77 68 65 6e 20 74 68 65 20 |.fixed when the |
00001a80 66 6f 72 6d 75 6c 61 20 69 73 20 72 65 70 6c 69 |formula is repli|
00001a90 63 61 74 65 64 20 6f 72 20 63 6f 70 69 65 64 2e |cated or copied.|
00001aa0 20 0a 0a 45 6e 74 65 72 20 61 20 73 69 6e 67 6c | ..Enter a singl|
00001ab0 65 20 73 6c 6f 74 20 72 65 66 65 72 65 6e 63 65 |e slot reference|
00001ac0 20 77 68 65 72 65 20 79 6f 75 20 64 6f 6e 27 74 | where you don't|
00001ad0 20 77 61 6e 74 20 74 68 65 20 0a 72 65 66 65 72 | want the .refer|
00001ae0 65 6e 63 65 20 75 70 64 61 74 65 64 20 64 75 72 |ence updated dur|
00001af0 69 6e 67 20 65 76 61 6c 75 61 74 69 6f 6e 3b 20 |ing evaluation; |
00001b00 65 6e 74 65 72 20 61 20 72 61 6e 67 65 20 0a 77 |enter a range .w|
00001b10 68 65 72 65 20 74 68 65 20 66 6f 72 6d 75 6c 61 |here the formula|
00001b20 20 69 73 20 74 6f 20 62 65 20 75 70 64 61 74 65 | is to be update|
00001b30 64 2e 0a 0a 41 73 20 6d 65 6e 74 69 6f 6e 65 64 |d...As mentioned|
00001b40 20 61 62 6f 76 65 2c 20 79 6f 75 20 6d 75 73 74 | above, you must|
00001b50 20 65 6e 74 65 72 20 61 20 72 61 6e 67 65 20 77 | enter a range w|
00001b60 68 65 72 65 20 79 6f 75 20 0a 77 61 6e 74 20 74 |here you .want t|
00001b70 68 65 20 63 6f 6e 64 69 74 69 6f 6e 20 75 70 64 |he condition upd|
00001b80 61 74 65 64 20 66 6f 72 20 65 61 63 68 20 73 6c |ated for each sl|
00001b90 6f 74 20 69 6e 20 61 20 64 61 74 61 62 61 73 65 |ot in a database|
00001ba0 20 0a 66 75 6e 63 74 69 6f 6e 3a 0a 0a 20 50 44 | .function:.. PD|
00001bb0 33 3a 20 20 64 73 75 6d 28 61 31 61 31 30 30 2c |3: dsum(a1a100,|
00001bc0 20 27 62 31 3e 31 30 27 29 0a 20 50 44 34 3a 20 | 'b1>10'). PD4: |
00001bd0 20 64 73 75 6d 28 61 31 61 31 30 30 2c 20 20 62 | dsum(a1a100, b|
00001be0 31 62 31 30 30 3e 31 30 29 0a 0a 20 50 44 33 3a |1b100>10).. PD3:|
00001bf0 20 20 64 73 75 6d 28 61 31 61 31 30 30 2c 20 27 | dsum(a1a100, '|
00001c00 62 31 3d 22 66 72 65 64 22 27 29 0a 20 50 44 34 |b1="fred"'). PD4|
00001c10 3a 20 20 64 73 75 6d 28 61 31 61 31 30 30 2c 20 |: dsum(a1a100, |
00001c20 20 62 31 62 31 30 30 3d 22 66 72 65 64 22 29 0a | b1b100="fred").|
00001c30 0a 20 50 44 33 3a 20 20 64 73 75 6d 28 61 31 61 |. PD3: dsum(a1a|
00001c40 31 30 30 2c 20 27 62 31 3d 61 23 32 30 30 20 26 |100, 'b1=a#200 &|
00001c50 20 63 31 3e 31 30 29 0a 20 50 44 34 3a 20 20 64 | c1>10). PD4: d|
00001c60 73 75 6d 28 61 31 61 31 30 30 2c 20 20 62 31 62 |sum(a1a100, b1b|
00001c70 31 30 30 3d 61 32 30 30 20 26 20 63 31 63 31 30 |100=a200 & c1c10|
00001c80 30 3e 31 30 29 0a 0a 20 50 44 33 3a 20 20 64 73 |0>10).. PD3: ds|
00001c90 75 6d 28 61 31 61 31 30 30 2c 20 27 24 62 31 3d |um(a1a100, '$b1=|
00001ca0 24 61 24 23 32 30 30 20 26 20 24 63 31 3e 24 61 |$a$#200 & $c1>$a|
00001cb0 24 23 32 30 31 27 29 0a 20 50 44 34 3a 20 20 64 |$#201'). PD4: d|
00001cc0 73 75 6d 28 61 31 61 31 30 30 2c 20 20 24 62 31 |sum(a1a100, $b1|
00001cd0 24 62 31 30 30 3d 24 61 24 31 30 30 20 26 20 24 |$b100=$a$100 & $|
00001ce0 63 31 24 63 31 30 30 3e 24 61 24 32 30 31 29 20 |c1$c100>$a$201) |
00001cf0 0a 0a 0a 0a 0a 0a 0a 0a 69 66 28 31 2c 32 2b 32 |........if(1,2+2|
00001d00 2c 33 2b 33 29 0a 30 20 26 20 33 2b 33 0a 32 2b |,3+3).0 & 3+3.2+|
00001d10 32 20 7c 20 33 2b 33 20 69 66 28 30 2c 33 2b 33 |2 | 3+3 if(0,3+3|
00001d20 2c 32 2b 32 29 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a |,2+2)...........|
00001d30 0a 0a 0a 0a 0a 0a 0a 53 74 61 72 74 2d 46 69 6e |.......Start-Fin|
00001d40 69 73 68 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a |ish.............|
00001d50 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a |................|
00001d60 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 6e 6f 74 |.............not|
00001d70 68 69 6e 67 20 28 62 6c 61 6e 6b 20 63 65 6c 6c |hing (blank cell|
00001d80 29 0a 74 65 78 74 20 73 6c 6f 74 3a 20 48 65 6c |).text slot: Hel|
00001d90 6c 6f 0a 6e 75 6d 62 65 72 20 73 6c 6f 74 3a 20 |lo.number slot: |
00001da0 22 48 65 6c 6c 6f 22 0a 6e 75 6d 62 65 72 20 73 |"Hello".number s|
00001db0 6c 6f 74 3a 20 30 0a 6e 75 6d 62 65 72 20 73 6c |lot: 0.number sl|
00001dc0 6f 74 3a 20 22 20 22 0a 25 43 4f 3a 43 2c 31 32 |ot: " ".%CO:C,12|
00001dd0 2c 34 38 25 25 43 4f 3a 44 2c 31 32 2c 33 36 25 |,48%%CO:D,12,36%|
00001de0 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a |................|
*
00001e40 6f 72 0a 6f 72 0a 6f 72 0a 25 43 4f 3a 45 2c 31 |or.or.or.%CO:E,1|
00001e50 32 2c 32 34 25 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a |2,24%...........|
00001e60 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a |................|
*
00001ef0 0a 0a 0a 0a 0a 0a 0a 0a 0a 25 48 32 25 50 44 20 |.........%H2%PD |
00001f00 33 0a 0a 30 0a 48 65 6c 6c 6f 0a 22 48 65 6c 6c |3..0.Hello."Hell|
00001f10 6f 22 0a 30 0a 30 0a 0a 31 0a 48 65 6c 6c 6f 0a |o".0.0..1.Hello.|
00001f20 22 48 65 6c 6c 6f 22 0a 31 0a 31 0a 0a 42 6c 61 |"Hello".1.1..Bla|
00001f30 6e 6b 0a 4e 6f 74 20 65 6d 70 74 79 0a 4e 6f 74 |nk.Not empty.Not|
00001f40 20 65 6d 70 74 79 0a 42 6c 61 6e 6b 0a 42 6c 61 | empty.Blank.Bla|
00001f50 6e 6b 0a 25 43 4f 3a 46 2c 31 32 2c 30 25 0a 0a |nk.%CO:F,12,0%..|
00001f60 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a 0a |................|
*
00002000 0a 0a 25 48 32 25 50 44 20 34 0a 0a 62 6c 61 6e |..%H2%PD 4..blan|
00002010 6b 0a 22 48 65 6c 6c 6f 22 0a 22 48 65 6c 6c 6f |k."Hello"."Hello|
00002020 22 0a 30 0a 62 6c 61 6e 6b 0a 0a 31 0a 53 74 72 |".0.blank..1.Str|
00002030 69 6e 67 20 6e 6f 74 20 65 78 70 65 63 74 65 64 |ing not expected|
00002040 0a 53 74 72 69 6e 67 20 6e 6f 74 20 65 78 70 65 |.String not expe|
00002050 63 74 65 64 0a 31 0a 31 0a 0a 42 6c 61 6e 6b 0a |cted.1.1..Blank.|
00002060 4e 6f 74 20 65 6d 70 74 79 0a 4e 6f 74 20 65 6d |Not empty.Not em|
00002070 70 74 79 0a 42 6c 61 6e 6b 0a 42 6c 61 6e 6b 0a |pty.Blank.Blank.|
00002080 
.