Pre Decimal Currency - Calculations in Excel

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

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

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 V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Ken" <[email protected]> wrote in message
news:[email protected]...
>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

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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Ken" <[email protected]> wrote in message
news:[email protected]...
>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

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

"Bernard Liengme" wrote:

> 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
>
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Ken" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>

Thank you Jon for your reply.

Please see my reply to Bernard below for more information.

Ken

"Jon Quixley" wrote:

>
> 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
>
>
> --
> Jon Quixley
> ------------------------------------------------------------------------
> Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
> View this thread: http://www.excelforum.com/showthread...hreadid=550314
>
>

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


"Jon Quixley" wrote:

>
> 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
>
>
> --
> Jon Quixley
> ------------------------------------------------------------------------
> Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803
> View this thread: http://www.excelforum.com/showthread...hreadid=550314
>
>

Ken, asssuming you have pounds in A2:A10, shillings in B2:B10, pence in 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)

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

[email protected]
[email protected] with @tiscali.co.uk


"Ken" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Bernard Liengme" wrote:
>
>> 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
>>
>> --
>> Bernard V Liengme
>> www.stfx.ca/people/bliengme
>> remove caps from email
>>
>> "Ken" <[email protected]> wrote in message
>> news:[email protected]...
>> >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
>>
>>
>>

> 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!!

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" wrote:

>
> Ken, asssuming you have pounds in A2:A10, shillings in B2:B10, pence in
> 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)
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=550314
>
>

Originally Posted by Ken

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

Hi Ken,

the formulas should "carry" correctly, that's effectively what the MOD function does, here's an example showing some sample data with the 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 4