Pre Decimal Currency - Calculations in Excel

[Guest]

I want to replicate some historical bookeeping in Excel. The currency I want
to use is pre-decimal Australian Pounds Shilling and Pence where 2 Halfpenny
= 1 Penny, 12 Pence = 1 Shilling and 20 Shillings = 1 Pound.

Does anyone know how this can be done?
Ken

 

[Jon Quixley]

Ken,

It rather depends what you want to do with the answers. Obviously I
would think that you want to get the figures you have into something
that the computer can use, add them up etc. Expressing the results back
as LSD might be a bit more tricky.
I think that the easiest though perhaps not the prettiest way is to
segregate the pounds from the shillings and the pennies. Once you have
this, converting it to something usable is easy.
Enter the pounds into column A, the shillings into column B and pennies
in column C. To obtain the total expressed as a decimal pound you need
to express the shillings and pennies as fractions of the pound. The
formula in column D would be =+(c9/12/20)+b9/20+a9. For an input of
£5-15-8, you should get 5.78333. This will enable you to convert
the LSD format into something that the computer can understand - is
this what you were looking for or is it something more complex?

Cheers
Jon

 

[Bernard Liengme]

I recall from my youth (put barely!) that any arithmetic operation other
than addition and subtraction required first converting LSD values to pence.
Then we did the math and converted back to LSD. (for US readers: L stands
for pound £, S for shilling and D for denier - Roman penny and all this
happened long before the other LSD!!!)
If I had you task, I would write two macros: one to convert something like
24\18\10 1/2 to a fractional pound value (24.94375); the other to convert
back
Note I would entry values using non-traditional backslashes to stop Excel
think about dates.
best wishes

 

[Bernard Liengme]

Here are two User-defined functions for you. ToDecimal takes a value such as
28\18\10 1/2 and returns 24.94375, or 2\10\4 and returns 2.51667 (I have
shown values rounded to 6 places). The second one takes decimal pounds such
as 24.94375 and returns the textual value. Note the backward slash to avoid
Excel thinking about dates - I used regular slash fro halfpenny and
farthing. You could easily change function to work with hyphens.

Send me personal email and I will forward a file with these.
best wishes

Function ToDecimal(lsd) As Double
x = InStr(1, lsd, "\")
pounds = Val(Mid(lsd, 1, x - 1))
y = InStr(x + 1, lsd, "\")
shillings = Val(Mid(lsd, x + 1, 2))
pounds = pounds + shillings / 20
pence = Val(Mid(lsd, y + 1, 2))
pounds = pounds + pence / 240
fraction = Right(lsd, 3)
If fraction = "1/2" Then
pounds = pounds + 0.5 / 240
End If
If fraction = "1/4" Then
pounds = pounds + 0.25 / 240
End If
ToDecimal = pounds
End Function

Function ToLSD(metric)
pounds = Int(metric)
metric = (metric - pounds) * 20
shillings = Int(metric)
metric = (metric - shillings) * 12
pence = Int(metric)
fraction = Application.WorksheetFunction.Round(metric - pence, 2)
If fraction = 0.5 Then
coin = "1/2"
ElseIf fraction = 0.25 Then
coin = "1/4"
End If
ToLSD = pounds & "\" & shillings & "\" & pence & " " & coin
End Function

 

[Guest]

Thank you for your reply Bernard.

I was actually hoping to total a column(s) of LSD. Also thinking that one
way would be to keep the denominations in separate columns.

If this were so the one would have to reset the cell to zero at 12 and 20
respectively for pence and shillings and carry 1 to the cell or column on the
left.

Am I on the right track?

It appears that you suggest converting to decimal, doing the calculation
then reconverting back to LSD. Am I correct?

How would this work with a column of figures? e.g.
L S D
25 14 6
3 5 -
2 6
4 0 5
....and so on?

Ken

 

[Guest]

Thank you Jon for your reply.

Please see my reply to Bernard below for more information.

Ken

 

[Guest]

Jon,

The first part seems to work out. Column D totals ok.

Then I tried using the INT function on the total. I think the whole number
becomes the total of the £ column. Btw Alt+0163 inserts £.
Then I subtracted the integer from the total leaving only the decimal portion.
I multiplied this by 20 for the shillings (20 shillings to the pound), the
integer of which I think might be the shillings????
Again multiplying the decimal portion by 12 I happened to end up with 13
pence which is not good.

I must get out the pen and paper and think like I was back in primary school
before before the 14th February 1966 :-(

Ken
Ken

 

[daddylonglegs]

Ken, asssuming you have pounds in A2:A10, shillings in B2:B10, pence i
C2:C10 you can use these formulas in A12, B12 and C12 respectively

=SUM(A2:A10)+INT((SUM(B2:B10)+INT(SUM(C2:C10)/12))/20)

=MOD(SUM(B2:B10)+INT(SUM(C2:C10)/12),20)

=MOD(SUM(C2:C10),12

 

[Sandy Mann]

I liked Bernard's idea of converting all the moneys to pennies.

With the L, S, D headers in F1:H1 and the amounts in F2:H5 try:

For whole pounds:
=INT(SUMPRODUCT((F2:F5*240)+(G2:G5*12)+H2:H5)/240)

For whole shillings:
=INT(MOD((SUMPRODUCT((F2:F5*240)+(G2:G5*12)+H2:H5)/12),20))

And the pence:
=MOD(SUMPRODUCT((F2:F5*240)+(G2:G5*12)+H2:H5),12)

Now I see why we went decimal!


Incidetally, as I remember it was *Decimal Day* not 14 February 1971?
--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[MartinW]

Incidetally, as I remember it was *Decimal Day* not 14 February 1971? > --

Hi Sandy,
Decimal day was in 1966 in Australia, here's the words to the song
sung to the tune of "Click go the shears" by the inimitable "DOLLAR BILL"


In come the dollars and in come the cents
To replace the pounds and the shillings and the pence
Be prepared for the change when the coins begin to mix
On the fourteenth of February 1966.

Clink go the coins, boys, clink, clink, clink
Change over day is closer than you think
Learn the value of the coins and the way that they appear
And things will be much smoother when the decimal point is here.

In come the dollars and in come the cents
To replace the pounds and the shillings and the pence
Be prepared folks when the coins begin to mix
On the fourteenth of February 1966.



Woops showing my age!!

 

[Guest]

Hi,

I like your elegant one cell approach for the Totals. Forgive me if I am
wrong but your formula does not seem to carry. e.g. When the pence reaches or
exceeds 12 a 1 should be carried into the Shillings column. Same lack of
carry into the £ column.

With this correction I'd like your solution the best.

Ken

 

[daddylonglegs]

Hi,

I like your elegant one cell approach for the Totals. Forgive me if
am
wrong but your formula does not seem to carry. e.g. When the penc
reaches or
exceeds 12 a 1 should be carried into the Shillings column. Same lac
of
carry into the £ column.

With this correction I'd like your solution the best.

Ken

Hi Ken,

the formulas should "carry" correctly, that's effectively what the MO
function does, here's an example showing some sample data with th
results obtained by the formulas I suggested

L S D
4 12 11
6 15 10
2 13 4
5 0 10
9 7 9
2 5 7
3 9 11
2 17 4
6 5 10

43 9

 

[baluo]

Hi, I am a newbie in this forum.

This thread is already a few years old, but I hope somebody here can tell me how to subtract expenses from income in the LDS format.

Many thanks already for this formula!

I need to create an account with historical payments (mid-1800s) and would be grateful for a "sum" function between the total income and expenses.

Gerhard (Australia)

Ken, asssuming you have pounds in A2:A10, shillings in B2:B10, pence i
C2:C10 you can use these formulas in A12, B12 and C12 respectively

=SUM(A2:A10)+INT((SUM(B2:B10)+INT(SUM(C2:C10)/12))/20)

=MOD(SUM(B2:B10)+INT(SUM(C2:C10)/12),20)

=MOD(SUM(C2:C10),12

--
daddylongleg