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 : –



Rosebud Grates for Live Steam Locomotives

The Rosebud Phenomenon

I recently had a discussion with a fellow model club member about fitting a Rosebud grate to a Polly V locomotive.   There is quite a lot of discussion on various forums of this style of grate so I won’t repeat what has already appeared in the likes of Model Engineer.   The gist of the design is to replace conventional live steam bar type grates with a plate having a matrix of holes with back countersinks such as to occupy around 15% of the grate area.   The effect of this change is to get better combustion of the fuel and better efficiency.   Most users report only a fine powder residue after steaming and have observed that the fire appears to ‘float’ on the plate surface.  The back side countersinks appear to create a sort of Venturi effect to boost the draught to the fire.

How to Calculate the 15% matrix

So all this got me thinking.  This would be an easy job to run on the Tormach and all I needed was the design entering on Fusion 360.   Which brought me round to the calculation of the 15% surface area for the holes on the new rosebud plate.  Those who know me will bear witness to my weakness for doing spreadsheets and this little problem suggested a spreadsheet was needed.

Below is a simple sketch of a rosebud fire grate with dimensional attributes.  In the calculation I have allowed for a border around the holes in case there are any no-go areas for the hole matrix.  I have now updated the spreadsheet to allow holes to be ignored such as where used for mounting pillars.   I also give the XY coordinate of the corner holes relative to material centre to help the machining layout.

Below is a screen shot of the resulting rosebud grate spreadsheet and you can download it as a ZIP file via the following link – rosebud_grate_calculator 2

Here is the finished Fusion 360 drawing ready to run on the Tormach.  This shows the bottom surface with the 4mm through holes having been half depth countersunk.   Clearly four of these holes will need to be sacrificed for mounting legs onto the locomotive ash pan and these are removed from the above calculation.


So all is in place ready to cut metal and I will keep you posted on the progress and steaming results in due course.  (There is a slight problem at the moment in that we have a steaming ban in place because of all the dry vegetation at the club track).

Similar or related subjects : –

Verified by ExactMetrics