Forum Home
Press F1
 
Thread ID: 26799 2002-11-05 12:57:00 Pounds, shillings, pence, halfpennies and farthing sums in Excel 95 Deebee (1184) Press F1
Post ID Timestamp Content User
96275 2002-11-05 12:57:00 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. 		Deebee (1184)
96276 2002-11-05 19:43:00 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 .

robo . 		robo (205)
96277 2002-11-06 02:08:00 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. 		Graham L (2)
96278 2002-11-06 02:21:00 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) 		Deebee (1184)
96279 2002-11-06 02:31:00 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 		Graham L (2)
96280 2002-11-06 02:34:00 Romanis eunt domum..... robo (205)
96281 2002-11-06 08:22:00 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 !! Crunn (1068)
1