Fish record calculator

[The Monk]

Hi, I am trying to make a worksheet that will log the fish I catch in lbs, ozs and drams then calculate that cell as a percentage of the record fish of that species but can't even get to format a cell as weight. I'm a novice on Excel and wondered if any genius out there could do this for me. I have uploaded a .jpg of what I am trying to do for example:
B2 = 5lb 4oz 0drms
C2 = 1lb 15oz 06drms
D2 = percentage C2 is of B2
if any one could do this for me that would be greatly appreciated

Thanks The Monk

[6StringJazzer]

You may find some useful information in this thread

http://www.excelforum.com/excel-gene...g-weights.html

with a problem very similar to yours.

[squiggler47]

its a bit long but :-

=(LEFT(C2,FIND("lb",C2)-1)+SUBSTITUTE(MID(C2,FIND("lb",C2)+3,2),"o","")/16+SUBSTITUTE(MID(C2,FIND("oz",C2)+2,3),"d","")/256)/(LEFT(B2,FIND("lb",B2)-1)+SUBSTITUTE(MID(B2,FIND("lb",B2)+3,2),"o","")/16+SUBSTITUTE(MID(B2,FIND("oz",B2)+2,3),"d","")/256)

will allow you to type your B2 and C2 cells exactly as they are! (ie 5lb 4oz 10drm)

=(LEFT(C2,FIND("lb",C2)-1)+SUBSTITUTE(MID(C2,FIND("lb",C2)+3,2),"o","")/16+SUBSTITUTE(MID(C2,FIND("oz",C2)+2,3),"d","")/256)

converts a value in this format to decimal pounds

you cant format a cell like this but you can enter values as text and convert to a value for calculation!

[The Monk]

Thanks very much, you are a genius squiggler47 your formula does exactly what I wanted, once again thank you so much for your time and effort it is very much appreciated.