Prev: Help with If Sum statement - i think....
Next: Convert percentage into margin
From: peter on 15 Apr 2010 08:55
I want to use pounds shillings and pence format in excel...is it possible and
if so how do I go about it?
From: Mike H on 15 Apr 2010 09:22
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.


"peter" wrote:

> I want to use pounds shillings and pence format in excel...is it possible and
> if so how do I go about it?
From: Bernard Liengme on 15 Apr 2010 12:17
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
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme




"peter" <peter(a)discussions.microsoft.com> wrote in message
news:1939577E-B420-4DC1-950D-01A1ACBF920C(a)microsoft.com...
> I want to use pounds shillings and pence format in excel...is it possible
> and
> if so how do I go about it?

 | 
Pages: 1
Prev: Help with If Sum statement - i think....
Next: Convert percentage into margin