In every part of historical scholarship, there is that thing that you must learn but no one ever teaches it to you. For scholars of British history who work before the 1970s, learning about how pre-decimal currency works is important, but it is not always taught. Sure, on the surface it is easy enough: £1=20s.=240d. and 1s.=12d. Pence were often quartered and halved using halfpennies (½d.) or farthings (¼d.).
Now explain that to your phone’s calculator or to Microsoft Excel.
The difficulty in calculating a multiple-decimal was one of the reasons Britain decimalized its currency. As computers became more useful and more widely available—of course still only to corporations, research institutions, and the government—it became increasingly clear that £sd added an additional layer of complexity when programming computers, especially for crunching any economic data. With some other considerations in mind as well, Britain converted their currency from the 1 unit = 20 subunits = 240 sub-subunits to the familiar 1 unit = 100 subunits or decimal system on Decimal Day, February 15th, 1971.
So how did people in the past do math using such a strange system?1
In the nineteenth century, books were written to help people do this math before people had access to mechanical calculators. In 1842, the accountant T. Martin published his book Pounds, Shillings and Pence; or, A Series of Money Calculations on a Novel System; Illustrated by Examples Shewing the Method of Performing them in the Mind, with Less than One-fourth of the Usual Labour. Martin wrote that he published the book “at a cheap rate, so as to be within the reach of every one … that part of Arithmetic which ought to be understood by all persons employed in a countinghouse, warehouse, or shop.”2 He had developed a system similar to multiplication and division tables to allow for quick and easy money arithmetic through different charts. These kinds of helpful charts are still used to teach multiplication but Martin’s book shows that a whole host of teaching tools were developed to help people add £sd which are no longer used today. And despite Martin’s assurances that the calculations only take “one-fourth of the usual labour,” it is still fairly complex.
For my Master’s, I built an excel spreadsheet to track the inventories of deceased seafarers in the British merchant service. This data often included details like the value of liquidated inventories, the wages owed to the seafarers, their debts, and a cumulative sum owed to the next of kin. In order to force excel to understand that the currency was actually currency, I reduced sums in £sd to their base sums in pence. These large numbers became meaningless, especially vis à vis the £sd numbers recorded in actual documents I was looking at, but it was troublesome to convert them back. I didn’t know there was an easier way.
Excel Formulas for Pre-decimal £sd Conversion
Pre-decimal currency can be read using Microsoft Excel or Google Sheets using the function formulas already built into their software. Using these formuals you can build a handy pre-decimal calculator, crunch £sd into decimals which computer-generated charts can understand, and generally wrangle pounds, shilling, and pence easily, just like Martin wanted in 1842. I should add a disclaimer that while I did figure out some of these calculations, the functions for £sd column calculator and the function for converting £sd to decimal £d were not done by me. I have credited the former, but I cannot find the credit for the second and will update this post when I do.
Pre-decimal £sd into decimal £d
Pre-decimal currency can be forced into decimal form (£d) using an excel or google sheets function. The main benefit to this is that the pound value is preserved, which allows the data to visually retain its value for the researcher even as it becomes calculable to a computer. For example, £1.10s.6d. becomes £1.525, showing its £1 and a half value. The same sum in pence is 366d., which tells the researcher nothing at a glance.
For £d value: =+(I4/12/20)+H4/20+G4
This will allow a pre-decimal value to be computed in a spreadsheet, meaning £sd values will be recognized as numbers by the program and can be computed as such.
In order to return the value (£d) to its original £sd form, three functions must be used:
For £ value: =ROUNDDOWN((G8),0)
For s. value: =ROUNDDOWN(SUM((G8-H8)*20),0)
For d. value: =ROUND((SUM((((G8-H8)*20)-I8)*12))*4,0)/4
Pre-decimal £sd Column Calculator
Another really helpful set of functions can be used together to add columns of £sd sums.3 I use this a lot to double-check the math of captains and pursers, but their calculations are rarely wrong.
In B19 for the column of £ values: =SUM(B16:B18)+INT((SUM(C16:C18)+INT(SUM(D16:D18)/12))/20)
In B9 for the column of s. values: =MOD(SUM(C16:C18)+INT(SUM(D16:D18)/12),20)
In C9 for the column of d. values: =MOD(SUM(D16:D18),12)
Reducing Pre-decimal £sd to Pence and Expanding Pence to £sd
Though the draw of reducing to pence is largely served by converting to decimals, these calculations can still be useful. Since they don’t require any decimals (unless necessitated by ha’pennies or farthings), they are simpler sums to calculate.
For £sd to d: =(C4*240)+(C5*12)+C6
To return from d. to £sd:
For £ value: =ROUNDDOWN(SUM(C10/240),0)
For s. value: =ROUNDDOWN(SUM((C10-(D10*240))/12),0)
For d. value: =SUM(C10-((D10*240)+(D11*12)))
Excelforum.com moderator daddylonglegs, Answer to query “Pre Decimal Currency – Calculations in Excel,” June 11, 2006, https://www.excelforum.com/excel-general/550314-pre-decimal-currency-calculations-in-excel.html.
Martin, T. Pounds, Shillings and Pence; or, A Series of Money Calculations on a Novel System. London: Simpkin, Marshall and Co, 1842. Google Books. https://books.google.ca/books?id=8FkOSVLDzlQC&source=gbs_slider_cls_metadata_7_mylibrary&redir_esc=y.
- Not really so strange: the value of British currency was based on Roman currency, which was based on the actual weight of Roman coins.
- T. Martin, Pounds, Shillings and Pence; or, A Series of Money Calculations on a Novel System (London: Simpkin, Marshall and Co, 1842), v. Google Books. https://books.google.ca/books?id=8FkOSVLDzlQC&source=gbs_slider_cls_metadata_7_mylibrary&redir_esc=y.
- Found on the excelforum.com, posted by user daddylonglegs in 2006, https://www.excelforum.com/excel-general/550314-pre-decimal-currency-calculations-in-excel.html