Adding in Pounds, Shillings and Pence

Until 1971, when Britain converted to decimal currency, every schoolchild was adept at adding in the three bases necessary to calculate sums in £sd. Today, however, few people have the knack. Although it is possible to convert prices before adding them, the result is not very accurate, because decimal prices have to be rounded to the nearest 2.4p. For the small sums involved adding Victorian food prices, this creates a very big margin of error. The sample spreadsheet shown here was developed in Lotus 1-2-3, but could be adapted for use in Microsoft Excel. It will add up to ten £sd prices (including halfpennies and farthings) and convert the total into £p.

 

A

B

C

D

E

F

1

 

 

£

s

d

£p

2

 

 

 

1

11.25

 

3

 

 

 

4

1.75

 

4

 

 

 

2

2.25

 

5

 

 

 

7

11

 

6

 

 

 

8

11.5

 

7

 

 

 

5

6.75

 

8

 

 

 

1

0

 

9

 

 

1

3

3

 

10

 

 

 

8

1

 

11

 

 

 

10

11

 

12

SUB TOTAL

 

1

49

59.5

 

13

CARRY

 

2

4

 

 

14

 

 

 

 

 

 

15

ANSWER

 

3

13

11.5

 

16

CONVERSION

 

 

 

 

£3.70

 

Using the @ key, insert the following calculated cells:

C12: SUM(C2..C11)
D12: SUM(D2..D11)
E12: SUM(E2..E11)

C13: INT((D12+D13)/20)
D13: INT(E12/12)
 

C15: SUM(C11..C13)
D15: +D12+D13-(C13*20)
E15: +E12-(D13*12)

F16: +C15+(D15/20)+(E15/240)
 

In Cell F16 go to ‘Range Properties’, select ‘#’, ‘Currency’ and ‘British Pound’

This spreadsheet will add up to 10 numbers; for bigger lists, adjust each calculated cell range (eg C2..C20) using the @ key.

Back to the Bureau de Change.

Copyright J Sainsbury plc, 2000.