Spreadsheet Compendium by Popular Request

I have had a few requests for a compendium of all my spreadsheets created over the months and years.   This will be a work in progress as new sheets are created and then added.   Here are the current contents.   

A collection of my popular spreadsheets

Click on the link below to download the ZIP file.

Spreadsheets Compendium Jan 2022

Similar or related subjects : –

Spreadsheet for setting a lathe compound slide angle

Oh No …… Not Another Spreadsheet …..

In the course of making miniature taper reamers to bore injector cones I initially struggled to set the compound slide angle sufficiently accurately.  DAG Brown’s book details using geometry to set the angle and once I grasped this concept things improved.  This is shown below and uses the Sine Rule.

The application method involves using a dial gauge set to zero when touched off on the workpiece. The cross slide readout is also zeroed at this position (this can be on a DRO if fitted or the Vernier scale). The compound is then moved a known distance along the workpiece before measuring the displacement needed on the cross slide to bring the dial gauge back to zero.   Here is a picture.  This is not rocket science and has been detailed on many other sources.

setting the compound angle using Sine Law
Graphical view of the setting technique.

By making the Y distance as large as possible, the resolution of the angular setting will be improved.   The distance for Y is best chosen to sit symmetrically either side of the ‘closed’ position of the compound. By standardising on a fixed Y distance the process can be made more repeatable for day to day use.  Angles can be committed to a lookup table and hence my idea to create a spreadsheet as detailed below.

On my Myford Super 7 I decided that a distance of 50mm for Y gave me a reasonable travel distance (+/- 25mm on the closed position).   This could be accurately measured using the compound Vernier scale dial (my Myford is a metric version).   

To speed up the measurement process I scribed a 50mm spaced start and finish mark together with a datum mark on the side of the compound.   The datum mark is on the protractor ring. These ‘scratchings’ are shown below.  (The extra cap head screw and pin are the Geo Thomas ‘Red Book’ compound lock mods).

These markings remove the need to tediously count revolutions of the Vernier scale when making an angular measurement.   I simply set the Vernier ring to zero at one mark and then keep winding the compound until the second mark is reached.  I then check once again on the Vernier ring scale. The movement distance is then finely adjusted to zero by referencing to the Vernier ring scale zero.  If you have a DRO on the compound this process becomes more flexible.

To mount the dial gauge I used my 3D printed gauge holder in the tool post.

With all the setup tasks decided, the spreadsheet was created.  I chose to have 0.5 degree steps through to 69.5 degrees (it could be extended beyond this).  I also added two standalone look up  calculations. One to allow a single angle to be spontaneously calculated and one to back check a measured X distance to find its associated resulting angle.  The latter would allow set up errors to be quantified to allow a knowledgeable ‘tap’ of the compound in the right direction.

The spreadsheet can use any units for Y as the table will automatically reflect this change.  Here is a screenshot of the table based on my chosen value for Y of 50mm.

The spreadsheet .XLSX file can be downloaded from this link.

Accurately setting the compound slide angle

Similar or related subjects : –

Shopping list spreadsheet that has other uses

This one is way off beam …. an enquiry for help appeared on the Model Engineering Workshop forum for a simple shopping list when buying groceries etc.   

Excel has a facility to create a special version of a spreadsheet called a pivot table.   In short this is a table that contains columns and lines but each column has a header that is automated to allow sorting of the table by that particular column.   Not only does the column sort the full table but you can refine the data to just show and sort lines of interest.   It is easy to use, no macro skills needed just click and sort. I love pivot tables …. well lets be honest I love Excel.  So many possible uses for lines and columns.

The spreadsheet is on my download page and is stored as a ZIP file.

The table allows you to have multiple entries for the same produce from different stores and for each store there is a location in the store and a price for the item in that store.   So you can have different sorts of bread from different stores and all at different prices.   To go shopping you put a ‘Y’ in the Buy column.  Here is a screenshot (with random pricing I entered to try it out).

Once you have the ‘Y’ on the items of interest you get a price for the total shop.   You can then click on the Buy column header down arrow and a dialogue box comes up that asks how you want to sort.  By selecting just the Y entries you get a unique list for today’s shop.   Once you have the Ys only, you can then sub sort by store and by aisle or location in the store.  Once you have something that suits your expedition, print the active area by highlighting and using Print Selection and off you go with a prompt of what you should be looking for as you walk round each store.

There are no macros, no complex formula and anyone can use it.

OK it is a bit over the top for shopping but as the years pass we tend to forget what we went to the shops for in the first place.   We also tend not to see the price creep from week to week. Maybe it will help someone ?

More importantly the pivot table could be modified to become a workshop asset register.  Change the headings to Item, Manufacturer, Source, Location, Price Paid (the price you paid or the price you told your wife you paid ?) etc and you begin to look organised.   The grim reaper arrives and your family now have a listing of what you had hidden away, where precisely and at what value. They are now a step ahead and they are less likely to get ripped off by the workshop clearance bandits.   Think about it, it could be time well spent.

Similar or related subjects : –

Lockdown Pallet Beehive by Jonathan Powell

My side interest in bees brought this booklet to my attention where the author Jonathan Powell describes how he created a beehive from a couple of scrap wooden pallets.

Beehive made from scrap wooden pallets
The beehive mounted in a tree. Length is around 1m.

The hive is intended just for the bees with no intention of humans taking honey. It provides them with a tree like cavity to inhabit in keeping with a natural hole in a tree.  We humans seem to chop down our trees at an alarming rate and the potential for ancient trees with cavities suitable for wild bees diminishes by the day.

The hive has an inner core and an outer core with an insulation layer between the two using the sawdust created in cutting the pallet material.  My engineering brain kicked in and below is a spreadsheet that helps calculate the dimensions required for a multi-sided structure having the required 40 litres volume that bees appear to prefer.

Plenty of time is available to make one of these before the spring swarming period starts.

Pallet Hive Calculator

I do not claim to be an expert on bees but my interpretation is that such hives with high levels of insulation lead to less stress in the occupants.    This in turn means they are less prone to disease.   There is also much published information about the stress induced by having high density clusters of ‘domesticated’ bees leading again to disease.   Having simple well insulated hives for the wild bees to populate in relative isolation to each other must help these problems albeit at the expense of man being less able to raid honey. 

Here is an interesting link on the interaction of wild bees and domesticated bees.

For those wanting an interesting read I recommend ‘The Honeybee Democracy’ by Thomas Seeley.   Fascinating book.

Similar or related subjects : –

Tormach Costing Sheet Update

Tormach changed their prices last year so I have updated the costing spreadsheet I created to reflect these changes. Note that the sheet now has the new M Series prices for the 770 and 1100.

Note that I believe I have interpreted their prices correctly but you can check this once you have placed a request for quotation and compare. Let me know if you spot any errors.

 

Similar or related subjects : –