If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
pounds shillings pence format in excel
I want to use pounds shillings and pence format in excel...is it possible and
if so how do I go about it? |
#2
|
|||
|
|||
pounds shillings pence format in excel
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? |
#3
|
|||
|
|||
pounds shillings pence format in excel
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(D24),12) C5 (shillings) =MOD((SUM(C2:C4)+INT(SUM(D24)/12)),20) B5 (pounds) =SUM(B2:B4)+INT((SUM(C2:C4)+INT(SUM(D24)/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" wrote in message ... I want to use pounds shillings and pence format in excel...is it possible and if so how do I go about it? |
Thread Tools | |
Display Modes | |
|
|