+ Reply to Thread
Results 1 to 12 of 12

Pre Decimal Currency - Calculations in Excel

  1. #1
    Ken
    Guest

    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

  2. #2
    Forum Contributor
    Join Date
    08-02-2005
    Posts
    102
    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

  3. #3
    Bernard Liengme
    Guest

    Re: Pre Decimal Currency - Calculations in Excel

    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




  4. #4
    Bernard Liengme
    Guest

    Re: Pre Decimal Currency - Calculations in Excel

    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




  5. #5
    Ken
    Guest

    Re: Pre Decimal Currency - Calculations in Excel

    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

    >
    >
    >


  6. #6
    Ken
    Guest

    Re: Pre Decimal Currency - Calculations in Excel

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


  7. #7
    Ken
    Guest

    Re: Pre Decimal Currency - Calculations in Excel

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


  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    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)

  9. #9
    Sandy Mann
    Guest

    Re: Pre Decimal Currency - Calculations in Excel

    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

    >>
    >>
    >>




  10. #10
    MartinW
    Guest

    Re: Pre Decimal Currency - Calculations in Excel

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



  11. #11
    Ken
    Guest

    Re: Pre Decimal Currency - Calculations in Excel

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


  12. #12
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Quote 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1