Jump to content

Excel Wiz needed.


Recommended Posts

Does any one here know how to creat a number formula in excel

What i want to do is create a number format for fish weights like 12lb 03oz 05dr for instance

i don't know if it can be done but i want it so if i enter a wieght in drams it will automatically convert to lb's and oz's and also a format that will support lb's and oz's if i want to enter an actual wieght.

so in breif one that will automatically work out drams to total weight 3600drams divide 16 divide 16 i suppose and a format that will automatically enter if i put 10-3-5 would put 10lb 03oz 5dr abit like what you can do with the date.

 

Or even if somebody has a template for storing fish records on would be a help

 

Thankyou in advance and i hope this is not the question that finally get you all stuck.

 

[ 10. April 2003, 11:09 PM: Message edited by: will this name be ok ]

Link to comment
Share on other sites

Excel can't handle things as you wrote you want to do them. But you could get fairly close with some effort.

 

Mainly, you are trying to use a spread sheet for database work and that isn't a particularly great idea if you have an alternative.

 

Do you have Access by chance? Or any other dB application?

 

If not, what version of Excel?

" My choices in life were either to be a piano player in a whore house or a politician. And to tell the truth, there's hardly any difference!" - Harry Truman, 33rd US President

Link to comment
Share on other sites

will this name be ok:

Does any one here know how to creat a number formula in excel

....so in breif one that will automatically work out drams to total weight 3600drams divide 16 divide 16 i suppose

You would not do any dividing, just subtracting and adding.

There would be a loop that would take the number of drams in a pound from the total and add 1 to the number of pounds each time, until there is less than a pound left. Then another loop would take the number of drams in an ounce from the remainder and add 1 to the number of ounces each time, until there is less than an ounce left. The remainder would be the number of drams.

You would then have to present the variables in the format you require.

I would think it could be done in VBA.

You would, perhaps, have to type the number of drams in one cell and have the formula in another.

 

Where is Loony???

Link to comment
Share on other sites

Bloody Hell. That was tough but here you go.

 

For a fish weight of 1lb 8oz 4dr = (388 Drams)

 

OK then.....

 

(In Excel)

Cell A1 = 388

 

To convert this to "1lb 8oz 4dr"

 

Copy this formula into B1

 

=INT(A1/16/16)&"lb "&INT(MOD(A1/16,16))&"oz "&SUM(A1-(((INT(A1/16/16)*16*16))+(INT(MOD(A1/16,16))*16)))&"Dr"

 

Hope that solves your first question?!?!

 

I'll have a go at question 2 now.

Link to comment
Share on other sites

Second Part....(Even tougher)

 

In Cell A8 type 10!3!5

Don't type 10-3-5 as Excel will probably convert this to a date. Using commas would treat it with thousand seperators etc. Use "!" to be sure!

 

Copy this formula into cell B8

 

=LEFT(A8,FIND("!",A8,1)-1)&"lb "&LEFT(RIGHT(A8,LEN(A8)-FIND("!",A8,1)),FIND("!",RIGHT(A8,LEN(A8)-FIND("!",A8,1)),1)-1)&"oz "&RIGHT(A8,LEN(A8)-LEN(LEFT(A8,FIND("!",A8,1)-1)&","&LEFT(RIGHT(A8,LEN(A8)-FIND("!",A8,1)),FIND("!",RIGHT(A8,LEN(A8)-FIND("!",A8,1)),1)-1)&","))&"Dr"

 

Hope that solves your second question?!?!

 

[ 11. April 2003, 11:55 AM: Message edited by: bitsbobs ]

Link to comment
Share on other sites

You could make things a lot easier for yourself if you were to join the 21st century and use metric!

The problem isn't what people don't know, it's what they know that just ain't so.
Vaut mieux ne rien dire et passer pour un con que de parler et prouver que t'en est un!
Mi, ch’fais toudis à m’mote

Link to comment
Share on other sites

Blimey thats bitsbobs is a bright spark i could never have worked that out. Thankyou that man.

 

I would of used metric but it is to help me store records for fishsim which does have the option of converting to metric but all the record are stored as lbs, oz's and dr,

 

once again a great big thankyou for your help who needs support pages when you have a forum like this.

 

PS is there any way of adding the formulas to excel permenently? I had a look at the custom section in the numbers but it would not let me paste it in.

 

[ 11. April 2003, 01:46 PM: Message edited by: will this name be ok ]

Link to comment
Share on other sites

Much impressed bitsbobs :cool:

Although I do Server and desktop support for a living, I'm hopeless with all that Excel macro stuff.

I always tell our users I'm a bit like Schumacher's mechanic - I can put his car together and make it go fast - just don't ask me to drive it! :):)

Where's the 'ANY' key?

Link to comment
Share on other sites

bitsbobs - awesome. Truly.

 

wtnbo - you'll still be better off using a database to store data.

" My choices in life were either to be a piano player in a whore house or a politician. And to tell the truth, there's hardly any difference!" - Harry Truman, 33rd US President

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

We and our partners use cookies on our website to give you the most relevant experience by remembering your preferences, repeat visits and to show you personalised advertisements. By clicking “I Agree”, you consent to the use of ALL the cookies. However, you may visit Cookie Settings to provide a controlled consent.