700 Spreadsheets

# # #

NB

All spreadsheets have been developed for personal use. There are NO guarantees given. All output is to be verified to the users own satisfaction.

# # #

Passcode Generator

W_PassCode.ods [25.68kb] approx. - suggests suitable passwords / passphrases. Note that W_PassCode is one hundredth the size of W_PassGen as only the ASCII symbols, letters and numbers are "stored".

For those looking before downloading a PDF file, W_PassCode.pdf [48.71kb] approx. PDF The PDF file is larger that the Spreadsheet however using any software other than PDF viewer.

W_PassCode uses the spreadsheet random number generator to calculate Pass codes based on the letters, number and symbols on the second tab 2_ASCII_Char.

Only two cells are to be changed by the user unless longer codes are required. Cell C1 is the cell that should be usually "changed." Once changed to, possibly the same number, recalculates the spreadsheet so it is equivalent of rolling a Die/dice again. In LibreOffice Apache Open Office Ctrl/Shift/F9 performs a recalculation of the sheet. This may or may not be available in other spreadsheets, hence provision of the Cell C1 "method."

Cell I6 determines the likelihood of a symbol in the pass code. 0 and there will be NO letters or numbers only symbols. 1000 and only letters and numbers no symbols. A setting of about 950 is a one in twenty chance of a symbol being in the code.

No effort has been made to eliminate characters which look alike such as "bar", |, lower case "ell", l, and upper case "eye",I. The ease of distinction is dependent up the font chosen particularly for the "output area" Cells G3:X5. Very careful inspection is required should "space" be a chosen code character.

The pass codes are displayed in groups of five for "ease of use." If the pass code is copied to a Notepad file the "spaces" can be deleted - do NOT save the Notepad file withe pass code in it. If saved copies will be available on a PC for inspection by prying eyes.

The user is at liberty to chose any group of cells should only, say, a 30 character code be required. Note It may be advisable always to have the first character a letter or number rather than a symbol. This is the reason that Cell I7 is in red and set to zero, 0.

For wanting longer pass codes copy and paste, say, Cells C87:K96, i.e. in groups of five cells to generate the codes. Copy Cells X1:X6 and amend as required along the top rows to display the codes. Both Rows 1:2 should increment in steps of five continuing both sequences.  Row 1 that is "vital" row 2 eases counting. 

# # #

Password - Passphrase Generator

W_PassGen.ods [2.94Mb] approx. - suggests suitable passwords / passphrases.

also

For those looking before downloading a PDF file, W_PassGen.pdf [75.4kb] approx. PDF

If a 20 character passphrase was required from the PDF I might have selected Cell D27 as the suggestion is composed of the parts of 3 words. Use any of the "Password Testers" in Cells G1:K3 or others to "get a feel" what are good passphrases.

As supplied the suggested main passphrase may vary from approximately 65 - 102 characters, this is entirely dependent on the random words suggested after calculation.

Many sites have limitations on passphrase length. W_PassGen can "split" the main passphrase in to sub phrases complying with the length limitations. Input the desired passphrase length in Cell C4

W_PassGen is set up so that any cell changes and the sheet is recalculated and new suggestions are made. This is to avoid any issue of the sheet being passed on or “hacked” and obtaining a passphrase. For convenience use the “Dice/die feature” of Cell C5 to change the suggested passphrase and "slices."

Should Error:502 be seen in the "suggestion area," Cells C20:G33 – change Cell C5 - roll the dice/die.

The value of Cell O24, indicates the number of "slices" of the passphrase.  The user can insert additional rows and use, say, "fifteenth" parts by adding rows. Since the main passphrase may have as few as 65 characters the maximum number of "slices" should be limited to a maximum of sixty or possibly less.

The "slice points" may also be varied by varying the values in Cells N24:N32. For "uniqueness" the user may set these to be, say, 1.5, 2.5, 3.5 etc.

Due to some sites NOT accepting spaces and symbols only numbers and letters are suggested in the passphrases. It is relatively trivial to select insert the "possible" symbols into a suggested passphrase. For us sceptics it adds another degree of randomness.

To keep from "prying eyes" in use I copy the PassPhrase to a, plain text editor, Notepad file for editing, say, adding symbols or spaces if allowed. The Notepad file is NOT saved as "others" may retrieve it. I then copy the passphrase from Notepad to my Pass Phrase manager.

There are more than 370_000 words as supplied. The words are a combination of both English and American English. A comparison of using the words supplied vs. a smaller dictionary of common words used by another password generator :-

370_000^3 =50.657E15 - Combinations of 3 words

    8_000^4 =  4.096E15 - Combinations of 4 words

I believe the following two features add to the "randomness" of the passphrase.

The likelihood of numbers,Cell B15, can be varied from 0-1000. If Cell B15 set to around 700 three numbers should occur on average in the passphrase.

The likelihood of a word, Cell B18, being converted from lower case to UPPERCASE can be varied from 0 to 1000. If Cell B18 set around 500 on average 50% of the words in the passphrase will be converted to UPPERCASE.

For those requiring a longer main passphrase "unhide" the rows and insert more columns between Columns L : M and then copy Column L to each inserted column in the 1_PassGen tab.

If desired users may add country names, place names, words from other languages or any other source into 2_Words tab the ensure uniqueness.

An alternative means of variation as supplied with 370_000+ words the user may decide rather than adding words only select from say 350_000 by changing the value of Cell C2.

If your LibreOffice suite is later than this the rand() function should be sufficient "as is."

" ... https://help.libreoffice.org/Basic/Rnd_Function_Runtime = This page was last edited 21:06:02, 2016-06-18 by LibreOffice Help user.

The Rnd function returns decimal fractions ranging from 0 (included) to 1 (excluded) according to a uniform distribution. It uses the Mersenne Twister 19937 random-number generator.    ... "

Microsoft in the newer versions of Excel also use the Mersenne Twister formula as part of the "random function." As such the newer versions of Excel should also be sufficiently random for secure passphrase generation.

# # #

PlanB$1_Y

PLAN_B$1_Y is a spreadsheet to assist with planning, anniversaries and shopping.

Plan B - you now have a Plan B when things go wrong!

B$1 is the cell the for the current date & time DO NOT CHANGE as all calculations are dependent on this cell.

Y is the column for the simplest of sorts after revising the dates and times at any point.

As well as “planning” I also find the Plan useful for recording the events from sticky yellow notes I wish to remember. I have left further development to the users imagination as the scope is unbounded :-) :-)

If used by a couple, Jack & Jill, rows can be copied or moved from sheet to sheet on Plan_Jack or Plan_Jill

However the dependent rows below the red sample rows must match on the individual sheets for the calculations to work. Jack and/or Jill need to co-ordinate the dependent rows on both sheets

When planning to say "build garage" break the task down so that they fit on one line as shown. So instead of many tasks on one line we have individual tasks on each line e.g. "excavate foundations" "build foundations" "build walls" "build roof" on separate rows.
Further instructions are on the spreadsheet pages..

Revised February, 2016 

Link to 01_Plan_B$1_Y_01.ods [43kb] approx.

Link to 01_Plan_B$1_Y_01.xls [87kb] approx.

# # #

Munro Mountains of Scotland

This spreadsheet shows the Ordnance Survey locations of the Scottish Munros and altitudes in metres.

Link to Munro_001.ods [62kb] approx.

Link to Munro_001.xls [86kb] approx.

For those looking before down loading two PDF files:-

Link to Munro_001_1.pdf [78kb] approx. Spreadsheet

Link to Munro_001_2.pdf [86kb] approx. The Map

For location purposes and to assist with scaling Wick, Scarinish, Port Askaig, Ullapool, Portree, Inverness, Aberdeen, Perth and Fort William are shown .

# # #

Bolt Masses

Link to Bolts_20160201_01.ods [39kb] approx.

Input the Number of bolts and Length of bolts in upper left cells and the masses are shown in the table. For normal use only these two cells should be edited.

BS4190:1967 Grade 4.6 Black Bolts
BS3692:1967 Grade 8.8 Precision High Tensile Bolts
BS4395:1969 Part 1 Grade 8.8 Part 2 Grade 10.9 - Shear Only - HSFG

BS EN 15048 Non-Preloaded Grade 8.8 Bolts
BS EN 14399 Preloaded Grade 8.8 Bolts (Bolt, Nut & Washer)
ASTM 490
ASTM 325

It should be noted that over tightening ASTM bolts one is likely to break the bolt so an "obvious failure".

For BS bolts other than HSFG BS 4395 & BS EN 14399 the nut threads are likely to be stripped giving the false impression of proper assembly rather than a failure.

# # #

Quantity Mass Take Off ( & Centre Of Gravity)

Link to Masses_20160201_01.ods [20kb] approx.

Used to determine Mass Take Off of quantities per item and the overall mass.

The Centre of Gravity for the overall items can also be determined if the X,Y & Z locations are provided.

For circular sections I provide the diameter in the length cell and diameter*pi()/4 in the breadth cell to facilitate checking as shown in item 2

# # #

Plot or Certificate of Title - Checks

Link to Plot_Check_03.ods [39.82kb] approx.

For checking "Plot Surveys" and "Certificates of Title." As can be seen typing errors may have crept into Certificates of Title. This could either cost or save money. From my sample of 13 Titles I believe 2 have errors.

The spreadsheet can provide information on the closing errors and also of the calculated area of a closed plot or parcel of land.

In an ideal world the surveys should be adjusted such that the closing error for a closed area is 0 for both X & Y axes.

Why closing error elimination has not not been done on older titles may have been purely due to the effort required in the days before computers and spreadsheets.

For newer titles I would merely suggest bad practice. An alternative viewpoint maybe that this demonstrates the quality (or lack of quality) of the survey. This may be so but the surveyors work is completed only when the closing errors have been distributed by means of the Compass (or Bowditch) rule and similar methods.

Modern, 2016, .Electronic Disance Measurement , EDM, equipment quote distance measurement errors of 1mm + 1 ppm (part per million) [ = 1mm per km - Willim]. Using these quoted errors if we measured a distance of 1km we would expect it to be within 2mm of the true value and for 3km within 4mm of the true value.

The units of measurement are metres. If other units such as feet are used rather than metres the area will be square feet. Similar for any other unit of measurement used.

As noted the spreadsheet now determines the Whole Circle Bearings on the basis in the N/S and E/W in the appropriate columns ( must be uppercase letters E N S W ) for each boundary line.

# # #

Determine Co-ordinates of Third Point, Intersection Point of Two Radii , from Two Known Points 

Link to Inter_Rad_01.ods [14.7kb] approx.

This is a simple spread sheet for determining the co-ordinates / location of a third point. Enter the co-ordinates of the two points and the radii / distances to the third and the options are calculated. The units of the Co-ordinates and radii/distances must be the same i.e. metres, millimetres, feet etc. .

As can be seen in Test Case 2 The calculations may error if the X & Y are exactly the same, one solution other than testing is to add .0000001 to one of the values.

The measurements are best made using a steel tape. When pulling a fabric tape I have been able to vary a 30m dimension by 100mm. If using a fabric tape it is best to use a spring balance to apply the same pull and check against a steel tape or known distance at this pull and adjust dimensions accordingly.

Note how much the co-ordinates vary by changing one of the radii by, say 10mm, this perhaps is the best demonstration on the need for accuracy in the measurements.

# # #

Humidity & Apparent Temerature

Link to Humidity_Willim_01.ods [81.4kb] approx.

This is a spreadsheet for determining the humidity and moisture content of air. I believe the results are at least as accurate if not rather more accurate than pyschrometric charts and such like.

01_Calc ... normal TAB for calculations. Only two inputs are required:- temperature, ^C and Relative Humidity, RH%. The input cells are highlighted in yellow.

Altitude, m and wind speed, m/s can be input if required. The input cells are highlighted in green. The Australian Apparent Temperature is also calculated for the input wind speed.

The various other TABs :-
02_Comfort - research into comfort conditions ....
03_Wet_Bulb - comparison of spreadsheet and Arizona calculations
04_ASHRAE - comparison of spreadsheet and ASHRAE calculations
05_MAC_Inst - comparison of spreadsheet and Machine Applications Corporation, MAC, calculations
06_Temp_RH_Td - example spreadsheet constructed from use or 01_Calc
Calc_1 - data DO NOT change
Calc_2 - data DO NOT change

For more details of each TAB refer to :- 9256 Humidity Australian Apparent Temperature

# # #

NB

All spreadsheets have been developed for personal use. There are NO guarantees given. All output is to be verified to the users own satisfaction.

# # #