View Full Version : Pounds, shillings, pence, halfpennies and farthing sums in Excel 95

06-11-2002, 12:57 AM
I'm a historian, and would like to sometimes play with old invoices, cashbooks etc. in Excel 95, keeping the amounts in their original figures.

After playing with a whole lot of nested TRUNCs, MODs SUMs and divisions by 12 and 20, I got the following addition to work:
2 . 3 . 4
30 . 15 . 8
13 . 8 . 1
17 . 6 . 3
5 . 0 . 7
68 . 13 . 11
(oops PressF1 drops out spaces) but after changing all the figures to negatives with a minus sign, I got -68. 7. 1, and haven't a clue how. I can do such sums in my head much faster than I can make Excel do it.

If only Willie Gates and his crew had of thought of this. Someone probably cracked the problem long ago, but a few hours of Googling didn't enlighten me, and I still have to include the halfpennies and farthings. Multiplications and divisions must be diabolically difficult.

Any hints?

By the way, I've done a spreadsheet which converts /s/d & farthings and NZ$/c in any decade from 1270 and year from 1800 to its 2001 NZ$ equivalent if anyone's interested in a copy.

E.g. 28.17.4 in 1837 sterling = NZ$3,130.28 in 2001.

06-11-2002, 07:43 AM
I did something like that once to convert the number of days since 1.1.1900 into a date.

Easiest way would be something like this:

=INT(B1/200) {gets you number of whole pounds, assuming is is 200 pence per pound, I'm a little young for that [don't get to say that very often nowadays]}

then INT((B1-INT(B1/200))/20) should give whole shillings

then B1-INT(B1/20) is pence remaining.

My brain is starting to hurt.

Addition and subtraction in it would be easier if you converted the pound, shilling, pence to pence the re-converted the result.


Graham L
06-11-2002, 02:08 PM
20 shillings to the pound, 12d to the shilling 240, pence to the pound, robo. And you call yourself an accountant. :D (Or I suppose that accountants don't have to know anything about realmoney ... if they work for Enron or other model companies they just invent anything: units don't matter).

But that's a sensible suggestion ... convert input, convert again for output. Depending on the numbers, the number of pennies might get a bit big (I think you'll only have about 15 digits ... If so, pounds with a decimal fraction will be easiest ... just amt=pounds + [(1/20)*shillings] + [(1/240)*pence] + [(1/960)* farthings ] then all operations are easy. At the end: pounds=INT(amt), shillings =INT((amt-pounds)*20) etc.

06-11-2002, 02:21 PM
Thanks guys -- AMT is something else for me to look at.

I did the sum above this way: The amounts were in A1:C5 and the answer A6:C6
A6: =SUM(A1:A5)+TRUNC((SUM(B1:B5)+TRUNC(SUM(C1:C5)/12))/20)
B6: =MOD(SUM(B1:B5)+TRUNC(SUM(C1:C5)/12),20)
C6: =MOD(SUM(C1:C5),12)

Graham L
06-11-2002, 02:31 PM
oops ... "amt" isn't a function (as far as I know) : I was using it as a variable name :-( But you see what we are getting at: make each Lsd amount into a single number, either an integer number (of farthings!) of a decimal format such as ppp.xxx on which you can perform any arithmetic easily, then extract out the Lsd components for output.

For your next trick, do it in Roman numbers. :D

06-11-2002, 02:34 PM
Romanis eunt domum.....

06-11-2002, 08:22 PM
Ah ! the days of proper money, when, with four farthings (1 penny), you could gorge yourself at the sweet shop and a thruppenny (3 pence) bag of chips (wrapped in the Daily Mirror of course, sans page 3) could feed half the family !! Happy days !!