PX-42F Spreadsheet
A spreadsheet for the PX-42F calculator, with Excel's formulas worked out in the calculator's own 34-digit decimals, and sheets saved as CSV files.
1. What it is
The Spreadsheet turns the PX-42F into a pocket spreadsheet: a grid of cells, A1 to Z999, where each cell holds a number, some text, or a formula that works something out from other cells. Change a number and every formula that depends on it changes too.
- The calculator's numbers. Formulas are worked out in 34-digit decimals, from the same library Plus42 computes with.
=0.1+0.2is exactly 0.3. - Excel's formulas.
=SUM(B2:B9),=IF(A1>0,A1,0),$A$1and the rest work as in Excel, with the same function names. - CSV files. Sheets are saved as CSV files on the calculator's drive. Make one on a computer, work on it on the calculator, and open it again in Excel or Numbers with its formulas still working.
2. Getting started
Installing
- The calculator needs the PX-42F-Plus firmware with app API version 4 or later.
- On the calculator, open Settings → USB Drive → Connect to Computer.
- Copy
SPREADSHEET.binintoBIN/APPSon the drive namedPX-42F. From the project folder,make installdoes it. - To try the examples, copy
BUDGET.CSVandSAVINGS.CSV(in the project'ssim/drive/BIN/APPS/SPREADSHEET/) intoBIN/APPS/SPREADSHEETon the drive. Make the folder if it isn't there. - Eject the drive, then press EXIT on the calculator.
Starting and leaving
Start it from Settings → Games & Apps → Apps → SPREADSHEET. It opens the sheet you had last, or an empty one called SHEET1 the first time.
To leave, press EXIT (the ON key). If the sheet has changes that aren't saved, it asks first:
If the Sleep Timer or an empty battery ends the app before you've saved, there's no time to ask. The changes are kept anyway, and the sheet comes back as you left it the next time you start the app. It says so on the bottom line; save it then to keep it.
3. The screen
- The top line shows the cursor's cell, such as
E7, and what's typed in it: the formula itself, not its result. While you type, it shows what you're typing, with a cursor. - The grid shows 10 rows and as many columns as fit across. The column letters are along the top and the row numbers down the left; the cursor's column and row are highlighted. Numbers are right-aligned and text left-aligned. A number too wide for its column shows as
####; make the column wider (section 9). - The bottom line shows the menu for the top row of keys. While you type it shows what the keys do, and after some actions a short message, until the next key.
4. Moving around
As in the HP-42S's matrix editor, ▲ ▼ move up and down, and the first two keys of the menu, ← and → on the screen, move left and right. Held down, they repeat. The grid scrolls to keep the cursor in view.
| Key | Does |
|---|---|
| ▲ ▼ | Up or down a row. |
| SHIFT ▲ ▼ | Up or down ten rows. |
| ← → (menu) | Left or right a column. |
| GOTO (menu) | Goes to a cell by name: type it, such as C12, then ENTER. |
| ENTER | Edits what's in the cell, on the top line. |
| ← (backspace) | Empties the cell. |
| EXIT | Leaves the app, asking first if there are changes to save. |
The menus
The top row of keys works the menu on the bottom line. SHIFT shows the second menu, for one key.
| Menu key | Does |
|---|---|
| GOTO | Goes to a cell by name. |
| COPY | Marks a block to copy; COPY again copies it (section 8). |
| PASTE | Puts what was copied at the cursor, or fills a marked block with it. |
| FILE | Save, Save as, Open, New sheet (section 10). |
| WIDTH | The cursor's column wider or narrower, with ▲ ▼. |
| FORMAT | How numbers are shown: General, Fix or Sci. |
| CLEAR | Empties the whole sheet, after asking. |
| CALC | Works every formula out again. Only RAND() gives a different answer. |
| HELP | A page of the keys, on the calculator. |
5. Typing
Starting
On the sheet, just start typing. A digit, . or an operator starts a new entry in the cursor's cell, in place of what was there:
- A number: type it. E types the exponent:
6.02E23. - A formula: STO types the
=it starts with. RCL starts one and points at a cell straight away (below). - Text: press ALPHA (SHIFT ENTER) first, for the letters.
- To change what's there: ENTER puts the cell's entry on the top line to edit.
ENTER stores the entry and moves the cursor down a row, ready for the next. EXIT drops it and leaves the cell as it was.
=D2/C2 into E2: STO LOG 2 ÷ √x…
Two keyboards
While you type, the keys work as one of two keyboards. ALPHA (SHIFT ENTER) switches between them, and the bottom line says which is on: 123 or ABC. SHIFT before a key types its character on the other keyboard, once.
123 is for numbers and formulas. The digits and + − × ÷ (as * /) are where they're printed, and the keys around them type what formulas need. The top row types the column letters A to E, so =A1+B1 needs no SHIFT.
ABC is for text. Each key types the letter printed on it, as in the calculator's ALPHA keyboard.
The other keys
| Key | Does, while typing |
|---|---|
| ▲ ▼ | Move the cursor left and right in the entry. With SHIFT: to its start or end. |
| ← | Deletes the character before the cursor. SHIFT ← clears the entry. |
| XEQ | The function catalog (on ABC: SHIFT XEQ). |
| RCL | Points at a cell or a block (on ABC: SHIFT RCL). |
| ENTER | Stores the entry. SHIFT ENTER (ALPHA): the other keyboard. |
| EXIT | Drops the entry. |
The function catalog: XEQ
XEQ lists every function, with what it takes and what it gives. ▲ ▼ move through the list (with SHIFT, a page at a time), and a letter key jumps to the first function starting with that letter. ENTER types the function's name and its ( at the cursor; EXIT goes back without typing. The list opens where you last picked from it.
Pointing at cells: RCL
Instead of typing a cell's name, point at it. RCL puts a highlight on the grid; move it with ▲ ▼ or 8 2 (up and down) and 4 6 (left and right). . starts a block there: move to its other corner. ENTER types the cell's name, or the block's, such as D2:D4, and goes back to typing.
=SUM( from the catalog, then RCL, to D2, ., down to D4.
D2:D4; COS types the ).When a formula can't be read
A formula that can't be read, such as =1+*2 or one with a ( left open, isn't stored. The calculator sounds its error tone, the entry stays on the top line, and its cursor is where reading stopped, to fix it there.
*.6. Formulas
A formula starts with =. It's written as in Excel, so a sheet works the same there.
| Part | Meaning |
|---|---|
12 -3.5 .25 6.02E23 | Numbers. |
B2 | A cell's value. An empty cell is 0; one with text gives #VALUE!. |
B2:D9 | A block, for functions such as SUM: from B2 to D9, row by row. |
$B$2 B$2 $B2 | The same cell; the $ only matter when the formula is copied (section 8). |
( ) | Grouping. |
- in front | Minus. As in Excel it comes before ^: =-2^2 is 4. |
% after | Divided by 100: =8% is 0.08. |
^ | Power, left to right as in Excel: =2^3^2 is 64. It shows as ↑ on the screen, the HP-42S's own character. |
* / | Times, divided by. |
+ - | Plus, minus. |
= <> < > <= >= | Comparisons: 1 if true, 0 if false. TRUE and FALSE are 1 and 0. |
Letters can be typed in either case: =sum(b2:b4) is =SUM(B2:B4). Spaces are allowed anywhere between the parts.
Errors
A formula that can't give an answer shows an error in its cell. A formula that uses that cell shows the same error.
| Error | Meaning |
|---|---|
#DIV/0! | A division by zero, or the AVERAGE of nothing. |
#VALUE! | Text where a number goes, or a block used as one number. |
#REF! | A cell past Z or row 999, or a copy that moved a name off the sheet. |
#NAME? | No function by that name. |
#NUM! | No answer (=SQRT(-1), =0^0) or too big. |
#CIRC! | The cell needs its own value, directly or through other cells. |
Working it out
Every formula is worked out again after every change, in any order: a formula can use cells below or to the right of it, and chains of formulas hundreds of cells long work too.
7. Functions
The functions have Excel's names. Those marked values take any number of numbers, cells and blocks, separated by commas: =SUM(B2:B9,D2,100). In a block they skip text and empty cells, as Excel does.
| Function | Gives |
|---|---|
SUM(values) | Total |
AVERAGE(values) | Mean |
MIN(values) | Smallest |
MAX(values) | Largest |
COUNT(values) | How many numbers |
PRODUCT(values) | All multiplied |
STDEV(values) | Standard deviation |
AND(values) | 1 if none is 0 |
OR(values) | 1 if any isn't 0 |
IF(test,yes,no) | yes if test isn't 0 |
ABS(x) | Without its sign |
SQRT(x) | Square root |
EXP(x) | e to the x |
LN(x) | Natural logarithm |
LOG(x,base) | Logarithm, base 10 |
LOG10(x) | Logarithm, base 10 |
SIN(x) | Sine, x in radians |
COS(x) | Cosine, x in radians |
TAN(x) | Tangent, x in radians |
ASIN(x) | Arc sine |
ACOS(x) | Arc cosine |
ATAN(x) | Arc tangent |
ATAN2(x,y) | Angle of point x,y |
SINH(x) | Hyperbolic sine |
COSH(x) | Hyperbolic cosine |
TANH(x) | Hyperbolic tangent |
PI() | 3.14159... |
RADIANS(deg) | Degrees to radians |
DEGREES(rad) | Radians to degrees |
INT(x) | Down to a whole number |
TRUNC(x,digits) | Cut after digits |
ROUND(x,digits) | Round to digits |
MOD(a,b) | Remainder, b's sign |
POWER(a,b) | a to the power b |
FACT(n) | n! = 1x2x...xn |
GAMMA(x) | Gamma, (x-1)! |
SIGN(x) | -1, 0 or 1 |
NOT(x) | 1 if x is 0 |
RAND() | Random, 0 to 1 |
- Angles are in radians, as in Excel. For degrees, convert:
=SIN(RADIANS(30))is 0.5,=DEGREES(ATAN(1))is 45. - IF only works out the branch it takes, so
=IF(A1=0,0,1/A1)never divides by zero. Without the third part it gives 0 when the test is 0. - ROUND and TRUNC take the digits after the point, 0 if left out; a negative count rounds to tens, hundreds:
=ROUND(1234.5,-2)is 1200. ROUND rounds halves away from zero. - MOD has the sign of its second number, as in Excel and the HP-42S:
=MOD(-7,3)is 2. - LOG is base 10, or the base given:
=LOG(8,2)is 3. - STDEV is the sample standard deviation (n−1), as Excel's STDEV.
- RAND gives a new number every time the sheet is worked out again.
8. Copying and filling
To copy a block: COPY marks it from the cursor's cell; move to its other corner, then press COPY again. The bottom line says how big a block was copied. PASTE then puts it with its top left at the cursor, in place of what was there.
Names move with a copy. A formula copied one row down refers to the cells one row down: =B4*2 copied from C4 to C5 becomes =B5*2. A name with $ stays: =B4*$B$1 becomes =B5*$B$1. $B1 keeps its column and B$1 its row. A name moved off the sheet becomes #REF!.
Filling. Mark a block first (COPY, then move), then PASTE: what was copied is repeated to fill it. Copy one row of formulas, mark the rows below it, and PASTE fills them all, as Excel's fill down does.
Emptying. ← empties the cursor's cell. With a block marked, ← or CLEAR in the menu empties the block. EXIT unmarks it.
9. Number format and widths
FORMAT (SHIFT menu) sets how numbers show, for the whole sheet:
- General (to start with): as many digits as the column has room for, up to 15, without zeros at the end. Very large or small numbers, or ones that don't fit, show as
1.5E-7. - Fix 0 to Fix 6: that many digits after the point, as the HP-42S's FIX: 2.5 in Fix 2 is
2.50. - Sci 1 to Sci 6: a digit, then that many after the point, then the exponent:
1.23E4.
Only the showing changes: every number keeps all its digits.
WIDTH (SHIFT menu) makes the cursor's column wider with ▲ or narrower with ▼, from 3 to 30 characters; ENTER is done. Columns start 9 wide, and four fit on the screen.
The app remembers the format and the widths, with the sheet you were on.
10. Files
Sheets are CSV files in BIN/APPS/SPREADSHEET on the calculator's drive, named NAME.CSV. FILE in the menu has:
| Choice | Does |
|---|---|
| Save NAME.CSV | Saves the sheet under its name. |
| Save as… | Saves it under a new name, typed on ABC: up to 20 letters, digits, - or _. From then on the sheet has that name. |
| Open… | Lists the .CSV files in the folder to open one. If the sheet has changes, it asks to save them first. |
| New sheet | An empty sheet, under a name you type. It asks about changes first. |
On a computer
In USB Drive mode, the sheets are in BIN/APPS/SPREADSHEET. A CSV file is the sheet as text, a row on each line, the cells separated by commas.
- Saving writes each cell as you typed it, formulas with their
=. Excel and Numbers open the file with the formulas working. - Opening reads files with commas, semicolons or tabs between the cells, such as Excel writes in different countries. In a semicolon file, numbers with a decimal comma (
1,5) are read as numbers. - A file saved by Excel holds the results, not the formulas: Excel's CSV doesn't keep them.
- Text with characters the calculator's fonts don't have, such as accented letters, shows
?in their place. They are saved as they were. - Text that looks like a number but should stay text, such as a part number
007, starts with an apostrophe:'007. The apostrophe isn't shown.
11. Limits
- Cells A1 to Z999, up to 1200 of them holding something; an entry up to 120 characters. Opening a file with more columns or rows leaves the rest out, with a message.
- Numbers: 34 significant digits, up to about 106144.
- Formulas: parentheses and functions inside each other up to 16 deep.
- No text in formulas: no
"quoted"strings, and a formula's result is always a number. - One number format for the whole sheet; no dates, no charts.
- The app runs in the calculator's 128 KB for apps; text in cells shares about 60 KB.
12. Quick reference
SHIFT HELP shows this on the calculator:
