Convert Between Real Money (£sd) and New Money (£p) using MOD and QUOTIENT

Create a new spreadsheet and then enter in A2, A3 and A4 the amount you want to convert from Real Money  (£sd) in to New Pence (np or just p). If you would prefer, there is a link at the end of this post to a copy of this example spreadsheet.

When doing a conversion from anything in one system to the equivalent in another system you need to reduce to the smallest whole amount. In this case Pennies.

As you know, there are 12 pennies in a shilling, and 20 shillings in a pound. This means there are 12*20 pennies  = 240 pennies in a  real money £. To calculate the total number of pennies, you multiply the number of pounds by 240, the number of shillings by 12 and then the number of pennies by one. Then add the totals. So the formula in  E2 is  “=((A2*240)+(B2*12)+C2)”.

To calculate the value in np, you must then divide that value by 2.4 and then divide this again by 100. Why divide by 2.4 and why 100? Well, there are 100np in a £. That is where the 100 comes from. 10np is the same as 2/- (Two shillings).  There are 2*12 pennies in two shillings. So 10np=24d, therefore 1np = 24/10d or rather 2.4d.

Converting  back the other way uses the same basic method, but needs two other functions you may not be familiar with. 

The first step is to find the number of np there are in the value you wish to convert. This is easy to do, by multiplying by 100 (remember, there are 100np in a £pound).  The second step is to convert this to real money. Using the same factor as before, you multiply by 2.4.

This is where things may get confusing, until  you practice the calculation.  From the number of pennies that are identified in C5, we need to know how many (full) pounds there are. Then how many (full) shillings there are left. Finally, we need to know how many pennies (if any) remain.

In the period around decimalisation, to calculate  real money from a value such as £57.30, you would know the  amount of pounds would stay the same.  You would then just have to work out the np side of the  decimal point would be. In this instance 30p. There were a few ways you would do this. You could look it up on a ready reckoner table given out by the government.  Or you would know that  if a shilling was 5p, two bob was 10p and so 30p is 3 lots of 10p so therefore the amount you are looking for is £57 6/-

To see examples of a ready reckoner, take a look at this site http://lewstringer.blogspot.com/2011/02/d-is-for-decimal-day-and-dandy-day.html  You can see how things were being priced up.

But what if the bit left wasn’t a round number, Say you wanted to know £57.39? I know that the extra 9p is 1/10d.

But how do you calculate this? Firstly, you will need a helper cell, which will have a formula saying how many old pennies there are left after the pounds have been divided by 240. We need this so we can work out the shillings. We then need to work out if there are any real pennies left.

In cell C6 the formula is  =MOD(C5,240). MOD is short for modulus and you can think of it being the remainder left after  you’ve done a division. For example in a sum 12 divided by 3, the answer is 4 with no remainder. But 14 divided by  3 the answer is still 4 but this time there is a remainder of 2. Because we are dividing by 240 we need the formula to help.

So if the figure in C5 was 13774 (the number of old pennies in £57.39) the modulus is 93.6. This is saying that after you’ve divided 13774 by 240 it goes 57 times with a remainder of 94. Don’t worry about any rounding error, this would have been resolved by a happeny if they were still in circulation.

The formula syntax is =MOD(dividend, divisor). The dividend is the number (or the cell containing the number) that is going to be divided. The divisor is the number (or the cell containing the number) that you are dividing by.

The other side of the coin, if you’ll pardon the pun, is the QUOTIENT function. This returns the whole number part of the division. It leaves out the remainder part. So in the example just given of 13774 being divided by 240, this function would return only 57. It would ignore the remaining 94.

The syntax of the QUOTIENT function is =QUOTIENT(dividend, divisor) In this case it is =QUOTIENT(C5,240) to give the pounds. To calculate the shillings and pence, we need to use the helper cell at C6. Using the formula =QUOTIENT(C6,12) in F6, and the formula =MOD(C6,12) in G6 (remember here we want the number of whole pennies) you can see that £57.39 works out at £57 7/ 10d in real money.

The file used in this example can be downloaded from here RealMoney.xlsx

The video is on YouTube and can be found at https://youtu.be/uazuwN8LRVY