Posts

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

Working with percentages

What does the word “percent”  actually mean?

One way to think of a percent is to think of it as being a proportion measured in hundredths. It is a sort of scale. 100 hundredths is all of something, 0 hundredths is none of something.

This may sound a bit strange, but if you imagine there are 100 pieces of chocolate on a table (my table) and 0 pieces of chocolate on another table (bad luck as that’s your table) I have 100% of the chocolate and you have 0%. I’m a reasonable person, and so I put 25 pieces on your table. I now have 75 hundredths  of the chocolate and you have 25. I have 75 out of 100, and you have 25 out of 100. We write this 75% and 25%.

That is all well and good, but 2 of our friends appear, and I feel obliged to give them each 25% of the chocolate. Note that is of 25% of the total chocolate that I started with, not what I have now. That would be 25% of 75. For now we all have 25 pieces.

Percentages can also be written out as a decimal, which is very useful in doing calculations. We already said that 100% is the same as 1 of something. 1 can also be written out as 1.00 (this is 1 to 2 decimal places). To work out 75% as a decimal, you can move the decimal point 2 places to the left. So start with 75.0 and move the point two places to the left and you get 0.750. Similarly for 25% you get 0.25. That is the way it works for any percent to decimal.

It is a bit more complicated if you want to express 5% as a decimal. You still move the point 2 places to the left, but as there is a “space” in the tens bit, so you have put a zero. Therefore, 5% is 0.05 as a decimal.

To find out what a %age of something is, you can multiply by the decimal. For example 25% of 100 is 0.25*100. You can see this is 25.

Say you weren’t willing to share your 25 pieces chocolate. So I offered our friends 25% each of mine. How may would we each have? The maths is the same, except we are looking for 0.25 of 75, instead of 100. Use a calculator to work this out and you will see our friends have 18.75 pieces each. Ok you have to imagine that the chocolate gets cut, but you get the idea. I’m still ahead of the game as you have 25, friend 1 has 18.75 and  Friend 2 has 18.75 means that I am left with the balance of 37.5.

This is all well and good, but in the real world no one has a table with a hundred pieces of chocolate on waiting to be divided between friends.

A common reason for using percentages is to work out sales taxes. When you go in to a retail hardware store and buy wood for a DIY project you get charged a retail price including sales tax (in the UK this is called VAT). However, if you go in to a trade hardware store, you’ll be charged a VAT exclusive price. Currently VAT is at 20%.

If you spend £237.39 VAT inclusive how much were the goods and how much did you pay in tax? VAT is currently 20% which can be written as 0.20 as a decimal. The cost of the goods is therefore 80% or 0.80 as a decimal.

To calculate this, you would multiply by 0.80 and 0.20 respectively. So 237.39*0.80=189.912  and 237.39*0.20=47.478 as we don’t have coins for tenths of a penny you, would round up. 189.91 + 47.48=237.39.

If you went in to a trade hardware store and bought £237.89 worth of goods, how much sales tax would you  have to pay? As 100% of the goods is £237.39 what would 120% of this be? Perhaps the easiest way to calculate this is to multiply the value of the goods by 1.2.

This would be 237.39*1.2=284.868 Rounded this is £284.87

To see a video of how Excel calculates percentages please see the YouTube video located HERE https://youtu.be/L8l-WhU-oRw

Getting text in to Excel (from a screen dump) using OCR in OneNote

The problem I am going to show you how to solve is to take data from a screen that doesn’t  have a “PRINT” or “EXPORT” function. You get data in to Excel from a screen dump via OneNote.

There could be several reasons why you can’t print so it might be worthwhile to get your IT department to look at the software – is there a training issue or should it be put to the supplier as a development of the product?

This is a generic workaround. Any Windows based computer that has Microsoft Office on will have OneNote. It is free to use.

I will use information taken from a website to demonstrate. This data is taken from a publically available source located at https://www.england.nhs.uk/statistics/wp-content/uploads/sites/2/2018/02/MAR-December-17-SPN-7832.pdf

The process should work from any website, web based document or piece of Windows software UNLESS print screen is disabled.

Step 1 Click on Print Screen – this copies a picture of the screen in to the clipboard

Step 2 Paste this image in to OneNote

Step 3 Right Click on the picture and Copy Text from Picture. OneNote will use OCR to convert this in to text (see picture below)

Step 4 Paste this text in to Excel which will be ready for editing.

Please view the accompanying video at https://youtu.be/lgugXY2MXuA

 

 

 

 

Measuring the area of a set of stairs

How much would  you need to spend on buying a carpet for your stairs?

It sounds like a fairly straight forward question, and many people would just get a carpet fitter to call round to give them a quote. In this example, you won’t. Just get a tape measure and measure one of your steps, then I’ll show you how to calculate the area needed.

Let us tackle this problem using some maths and some Excel. First using maths:-

If you look at your stairs, they are essentially a series of rectangles (also called oblongs). There are two rectangles per step. The vertical one is called the rise and the horizontal one is called the run.  Add the rise and run together will give you the length.

The area of a rectangle is calculated by multiplying the length by the width (or you could say depth times breadth, it is the same thing).  If we call length L, and width W the formula would be Area = L * W. So what ever our measured length is, and whatever our measured width is, we can work out the area.

To calculate the area of each step, measure the rise and the run and then multiply this by width of the stair. A typical rise would be around 20cm, and a typical run would be about 25cm (adding these together gives 45cm). The width of the stair will depend on circumstances. One at home would generally be between 90 and 120cm.

It doesn’t matter if you prefer centimetres, millimetres or feet and inches. You’ll find it easier to use just one unit and stick to it. I call this the one unit rule.

A typical step could be 45cm * 90cm. So if Area = L * W, if you substitute the letters for the numbers, you have Area = 45*90.

This works out at 4050 cm2 . We say this as “four thousand and fifty centimetres squared”. You could say “square centimetres”, it means the same thing.

You then count the number of stairs that you have, for sake of argument settle on 12, we know to multiply the area of one step by 12 times. So we calculate Area of Whole Carpet = Area of step * 12

This will give 48,600 cm2 This is a lot of centimetres squared. You couldn’t go in to your local carpet shop and ask for a square centimetre of carpet. That would be bonkers. They sell carpets in metres squared (AKA square meters) and the symbol is m2.

You have to convert 48,600 cm2 in to m2. To do this you divide it by 10,000. You can use a calculator if you want to, or write the number to one decimal place, and move the decimal point 4 places to the left (four places as there are four zeros in 10000).  48,600 can be written as 48,600.0  Moving the decimal point four places to the left will give you 4.86.

You will need to buy 4.86 m2 of carpet. To calculate the cost of the stair carpet, multiply the cost per metre squared by the area. Let us say the cost is £10.00 it means the carpet will be £48.60.

In practice your carpet shop will probably want to add an amount of carpet for wastage in cutting. I’m guessing you’d want to buy about 6m2. If you are going for a complex pattern that needs to be carefully laid, then call in an expert for advice.

Some carpet shops will sell their wares “by the linear metre”. This is because their carpets come at a standard width and if you need 5m you get 5m by the standard width. You also get a lot of wastage.

To create a spreadsheet to calculate this yourself in Excel enter the following

Cell Reference Enter Contents Format
A1  Leave Blank
A2 Length of RISE Text
A3 Length of RUN Text
A4 Total Length Text
A5 Width Text
A6 Area of Step Text
A7 Number of Steps Text
A8 Total carpet Text
A9 Convert to m2 Text
A10 Price Per m2 Text
A11 Total Text
B1 In cm Text
B2 20 Number
B3 25 Number
B4 =sum(b2:b3) Number
B5 90 Number
B6 =sum(b4*b5) Number
B7 12 Number
B8 =sum(b6*b7) Number
B9 =sum(B8/10000) Number
B10 17.99 Number
B11 =sum(b9*b10) Number

You’ll end up with a spreadsheet looking something like this:-

In cm
RISE 20
RUN 25
Length 45
Width 90
4050
# STEPS 12
48600
4.86
Price  £        17.99
Total  £        87.43

 

Play with changing the values, for example width and price to see what happens to the values

Please download an example spreadsheet from the Examples section

If you want to see a video of this being  demonstrated in Excel, please visit my YouTube channel here

https://youtu.be/LhK3mMjrTs8