I want to compile a spread sheet in Excel using historical financial data
which is the old sterling pounds shillings and pence currency abolished in UK
in 1971 for the decimal currency now in use.

You're going to have to enter the pounds, shillings and pence in separate
columns to start. Assuming you want to keep the totals in the old system
you'd then want to convert total pence to "shillings and pence" and add
these shillings to the shillings column.

As I recall under the old system there were 20 shillings to a pound and 12
pence to a shilling so...

The number of whole shillings that the total pence is equal to is =INT(P/12)
where P is the cell with the pence total. The remainder pence is calculated
with this formula: =MOD(P,12).

Then you'd have to convert the total shillings (including those picked up
from the pence column) to "pounds and shillings". The formula =INT(S/20)
would calculate the number of pounds where S is the cell with total
shillings. The remainder shillings would be =MOD(S,20). Add the pounds from
the shillings column to the pounds column.

Now we know why decimalisation was pushed through over a protesting public!

--
Jim
"Derek Jones" <Derek wrote in message
...
|I want to compile a spread sheet in Excel using historical financial data
| which is the old sterling pounds shillings and pence currency abolished in
UK
| in 1971 for the decimal currency now in use.