pounds shillings pence format in excel

  • Thread starter Thread starter peter
  • Start date Start date
P

peter

I want to use pounds shillings and pence format in excel...is it possible and
if so how do I go about it?
 
Hi,

I remember when the UK was dragged kicking and screaming into the decimal
age and looking back we were mad to resist getting rid of such an
anachronistic system as LSD. Thank heavens you don't want halfpennys and
Farthings. You don't want halfpennies and farthings do you!!!

OK

1 pound =240 old pennies
1 shilling = 12 old pennies

I doubt you'll get a format for this but this UDF will convert a decimal
Pounds amount into and LSD amount

Call with

=Dec_LSD(A1)
where a1 contains a decimal currency amount

Function Dec_LSD(amt)
pounds = Int(amt)
amt = (amt - pounds) * 20
shillings = Int(amt)
amt = (amt - shillings) * 12
pence = Int(amt)
Dec_LSD = pounds & " L " & shillings & " s " & pence & "d"
End Function
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Just for the sake of nostalgia I played with adding three LSD amounts.
Method 1
item £ s d
a 1 18 9
b 2 3 4
c 4 12 5
sum 8 14 6
D5 (pennies) =MOD(SUM(D2:D4),12)
C5 (shillings) =MOD((SUM(C2:C4)+INT(SUM(D2:D4)/12)),20)
B5 (pounds) =SUM(B2:B4)+INT((SUM(C2:C4)+INT(SUM(D2:D4)/12))/20)

Method 2
item £ s d = pence
a 1 18 9 = 465
b 2 3 4 = 520
c 4 12 5 = 1109
total 8 14 6 = 2094
In F2 (under 'pence' ) =B2*240+C2*12+D2
This is copied to F3 and F4
In F5 =SUM(F2:F5)
D5 (pennies) =MOD(F5,12)
C5 (shillings) =MOD((F5-D5)/12,20)
B5 (pounds) =INT(F5/240)
This method is useful if ,for example, you want 12% of the sum; just change
F5 to =SUM(F2:F5)*12%

hope some of this helps
 
Back
Top