×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Entering old shilling currency in Excel

## Entering old shilling currency in Excel

(OP)
I am working with old shillings & pence data in Excel which has the format - 1 s. 6 p. (=1 shilling 6 pence) where 12 pence = 1 shilling. It is a list of years, occupations and wages and I need to be able to sort on the currency ascending and also graph the trend of wages. But how do I make Excel understand the value of the shillings and pence currency?

PS, I am not an Excel expert at all! Thanks for any tips.

### RE: Entering old shilling currency in Excel

put the pounds, shillings and pence into seperate columns.

column a: 1s. 6p.
column b: =VALUE(LEFT(A1, SEARCH("s",A1) - 1))
column c: =VALUE(MID(L135, SEARCH("s", L135) + 2, SEARCH("p", L135) - SEARCH("s", L135) - 1))
column d: =B1 + C1/12

column d then becomes a number (in shillings) you can use for calculations.
to translate A1 back into your format, use:

=ROUNDDOWN(A1,0)&"s. "&ROUNDDOWN((A1-ROUNDDOWN(A1,0))*12,0)&"p."

to calculate the pounds as well, use:

=IF(A1>20,ROUNDDOWN(ROUNDDOWN(A1,0)/20,0)&"l. "&MOD(ROUNDDOWN(A1,0),20),ROUNDDOWN(A1,0))&"s. "&ROUNDDOWN((A1-ROUNDDOWN(A1,0))*12,0)&"p."

use the shilling (ie decimal) figure for the data on the chart, and the text representation for the data labels.

hope this helps.

mr s. <;)

why does it never do what is says it does in the manual?

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!