Summing £ s d

Question

I have some old finance analysis sheets that use £ s d (Pounds Shillings and Pence), and I want to reproduce these in an Excel spreadsheet.

e.g.

Jan                        Food               Travel          Car         Household         Total

1954                   £  s  d              £  s  d         £  s  d          £  s  d               £  s  d  

---

Eggs                       4  6                                                                                      4  6

Bus ticket                                    1  7  3                                                         1  7  3

Petrol                                                                2  5  6                                    2  5  6

Insurance                                                                           34  0  0             34  0  0

                        ------------------------------------------------------------------------------------

Totals                      4  6             1  7  3          2  5   6      34  0  0              37 16  9

But how can I sum the figures (both columns and rows) ?

I visualise having a series of sets of three columns, for £ s and d, but the total for the pennies must be the remainder of (the sum of the column divided by 12), with the extra shillings carried over and added to the sum of the shillings column - which itself is the remainder of  (the sum of the column divided by 20), with the extra pounds carried over and added to the sum of the pounds column.  And similarly for the rows, of course...

I guess I shall have to use a User Defined Function    :-(

Would this do ?

Function PennyTotal (PenceColumn as Range)    '  HOW would this work in summing a ROW ???

Shillings = Round(Sum(Range)/12, 0)

PennyTotal = Sum(Range) - Shillings * 12

End Sub

And similarly for the Shillings column - but how to "carry" ????

OR

The normal style of writing was  £7 4s 6p  or £7/4/6 or £7-4-6

Could I use one of  those formats to enter the amounts in a single column (not three), and then produce a UDF that would sum the column ?

How would you solve this ?

Answer 1

What, no halfpennies or farthings to worry about?

There are several different approaches.

It is possible to create formulas that will work with three £,s,d columns:

TotalD =MOD(SUM(Pence),12)

TotalS =MOD(SUM(Shillings)+INT(SUM(Pence)/12),20)

TotalL =SUM(Pounds)+INT((SUM(Shillings)+INT(SUM(Pence)/12))/20)

Which would do fine for the column totals but would be harder for the row totals as you would have to reference every 3rd cell to get the Pence range, for example.

---

An alternative would be to use a VBA function to add up £sd strings in single cells and provide the result as an £sd string.

Something like this, assuming the format of the string is £12-1-3

Function AddLSD(R As Range) As String

  Dim C As Range

  Dim TotPence As Long

  For Each C In R.Cells

    TotPence = TotPence + LSDToPence(C.Value)

  Next

  AddLSD = PenceToLSD(TotPence)

End Function

Function LSDToPence(LSD As String) As Long

  Dim vParts

  Dim Pence As Long

  vParts = Split(LSD, "-")

  If UBound(vParts) >= 2 Then

    If Left(vParts(1), 1) = "£" Then

      Pence = 240 * CInt(Mid(vParts(0), 2)) + 12 * vParts(1) + vParts(2)

    Else

       Pence = 240 * vParts(0) + 12 * vParts(1) + vParts(2)

    End If

  ElseIf UBound(vParts) >= 1 Then

    Pence = 12 * vParts(0) + vParts(1)

  ElseIf UBound(vParts) = 1 Then

    Pence = vParts(0)

  Else

    Pence = 0

  End If

  LSDToPence = Pence

End Function

Function PenceToLSD(Pence As Long) As String

  If Pence>=240 Then

    PenceToLSD ="£" & Int(Pence/240) & "-" & Int((Pence mod 240)/12) & "-" & (Pence Mod 12)

  Else

    PenceToLSD = Int(Pence /12) & "-" & (Pence Mod 12)

  End If

End Function

Then you can enter your data as £1-19-11 or £0-12-6 in a single cell, omitting the £ is OK, and if no pounds, you can use 12-6 (but that will probably be interpreted as a date if you don't pre-format the cell as text).

And you can use e.g. =AddLSD(B2:B5) to do the addition.

Answer 2

Here is an easy method assuming you have separate columns for pounds, shillings and pence. First install these three UDFs:

Public Function pence(p As Long) As Integer

Dim t12 As Long

t12 = 12

pence = p - t12 * Int(p / t12)

End Function

Public Function shilling(s As Long, p As Long) As Integer

Dim t12 As Long, t20 As Long

t12 = 12

t20 = 20

shilling = s + Int(p / t12)

shilling = shilling - t20 * Int(shilling / t20)

End Function

Public Function pound(L As Long, s As Long, p As Long) As Long

Dim t240 As Long, t12 As Long, tPence As Long

t240 = 240

t12 = 12

tPence = L * t240 + s * t12 + p

pound = Int(tPence / t240)

End Function

Next we assume that we have total pounds, total shillings, and total pence in three separate cells (A1, B1, and C1)

So:        1234   56   78

Then    =pound(A1,B1,C1)  will display 1237

and      =shilling(B1,C1) will display 2

and     =pence(C1) will display 6