Cell formating and summing imperial weights

Hi all,

Does anybody know how to format cells for using imperial weight
(pounds, ounces and drams) and how do I then summ these values to als
give the result in pounds, ounces and drams

--
Wood
-----------------------------------------------------------------------
Woody's Profile: http://www.msusenet.com/member.php?userid=575
View this thread: http://www.msusenet.com/t-187117693

0
11/9/2005 9:39:09 AM
excel 39879 articles. 2 followers. Follow

1 Replies
401 Views

Similar Articles

[PageSpeed] 2

On Wed, 9 Nov 2005 03:39:09 -0600, Woody <Woody.1ydm2c@no-mx.msusenet.com>
wrote:

>
>Hi all,
>
>Does anybody know how to format cells for using imperial weights
>(pounds, ounces and drams) and how do I then summ these values to also
>give the result in pounds, ounces and drams?

I don't believe you can do that with formatting.

Probably the simplest method is to either enter your data in three separate
cells; define a separator and enter it as a text string; or use a custom form
that takes care of the conversion.

Then store the data as units and fractions of units of one of the above.

Finally, convert it and display it.

For example, to enter 6lb 4oz 11dr you could enter the data as 6.04.11 or
6.4.11.

Then have a separate cell where you convert the result to drams:

The formula for that would be:

=LEFT(A1,FIND(".",A1)-1)*16*16+MID(A1,FIND(
".",A1)+1,FIND(CHAR(1),SUBSTITUTE(A1,".",
CHAR(1),2))-FIND(".",A1))*16+MID(A1,FIND(
CHAR(1),SUBSTITUTE(A1,".",CHAR(1),2))+1,5)

or, if you download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/, the simpler:

=REGEX.MID(A1,"\d+",1)*16*16+REGEX.MID(A1,"\d+",2)*16+REGEX.MID(A1,"\d+",3)

You can then SUM the values in drams, and convert them back for display:

If the SUM is in B4, then:

=INT(B4/16/16)&"."&INT(MOD(B4,16*16)/16)&"."&MOD(B4,16)


--ron
0
ronrosenfeld (3122)
11/12/2005 12:41:14 PM
Reply:

Similar Artilces:

Data, lookups, and sums
I have an Excel spreadsheet as follows: Column A Dates in dd/mm/yy format Column B Additional data (text) Column C Group of figures (a) Column D Additional data (text) Column E Group of figures (b) In another spreadsheet I have: Row 1 Months going horizontally in mmm-yy format from Feb-04 to Jan-05 Row 2 Sum of figures I would like the sum of figures to be calculated as Group of figures (a) and (b) depending on what the corresponding date is. I thought this would be similar to a lookup table. However, I need to be able to add the sum of the two figures, and I need to ...

Have user input converted to uppercase in same cell as input?
Hi all Noob Alert! Very new to spreadsheets! I have a simple form that requires data selected from a text description to be entered in a cell to give us a special product code. The code is upper case in the description but ppl are entering it in lower case, I need to convert the entry to uppercase to match our entry system, but I need a function to convert and display the converted text in the original cell. I looked at =UPPER function but that displays converted text elsewhere, if I use it in the same cell as input I want converted I get the circular error msg. Can someone perhaps ...

Open .wpd format files
How do I open .wpd attachment in Outlook 2002? I do not have Word Perfect, only Word on Windows XP computer. "Suinee" <Suinee@discussions.microsoft.com> wrote in message news:2FFDAFC0-2657-4C59-A498-48F16C5FC324@microsoft.com... > How do I open .wpd attachment in Outlook 2002? I do not have Word > Perfect, > only Word on Windows XP computer. So where are you getting stuck? Word can open WordPerfect 5 and 6 files.... try saving the attachment, open Word, in Word, do a File/Open and pick Files of Type - all files, it might open, or there might be ...

Nested formular to give a word answer based on 2 cells
I have a s/s which I want to produce a statement based on 2 cells G170 is a cell that contails a sum (if cells above are filled it it will add them up) I170 contains a cost if G170 & i170 are blank then do nothing If G170 is greater than 0 and I170 is greater than 0 then again do nothing but if G170 is greater than 0 and I170 is = < 0 then "No Charge Made" Can anyone help all I get is FALSE I have tried =if(g170<=0, and (i170=<0,No Charge made","")) Hi, =IF(AND(G170="",I170=""),"",IF(AND(G170>0,I170...

Custom Number Format #7
Hi I have a column in Excel that needs to be converted as follows, I think I can do this using "Format" - "Cells" - "Custom Number" but am not sure what to type in so it keeps the first character as text followed by 5 digits (adding a zero to the end) Account number:- A4578 B6789 C6892 E8900 The account numbers need to become 6 digit (zero at the end) but retain the starting letter HELP! Ta Julia If you just want to display the value with an added zero, use Format/Cells/Number/Custom @"0" You can convert the numbers using a helper co...

some kind of Conditional format?
Hi all, I've got an issue where I need a formula that when I put a date (or any value) in cell A1, the text 'Closed' is automatically entered in cell B1 - I'm sure I've seen it somewhere before (probably used it too), but I've searched in vain - can anyone help? TIA Dave Not conditional format, but in B1 you can use the formula =IF(A1="","","Closed") -- David Biddulph "Dave" <dave@nospam.com> wrote in message news:JwE7n.24409$AJ7.20557@newsfe17.ams2... > Hi all, > > I've got an iss...

problem with conditional formatting and VBA (excel 2007)
Hello, i have a problem with conditional formatting and VBA (excel 2007). at first here is a screenshot of my conditional formula http://img156.yfrog.com/i/bugjz.png/ as you can see, the formula depends on B1 but it is relative. so far so good. but if i go through the cells with VBA the formula for all cells is the same, (e.g. =B4="yellow") if i do the same thing in excel 2003, vba shows the correct formulas: =B4="yellow" =C4="yellow" =D4="yellow" =E4="yellow" =F4="yellow" is this a known bug or do i something w...

Auto Formatting Custom Number
I'm creating a form where you can enter a credit card number. I set a custom number for the cell and described it as ####-####-####-####. You can enter a 16 digit number just fine, but when you tab away from the cell and the custom number takes place, it changes the last digit to a "0". Has anyone else encountered this? How do I fix it? -- CSBUG ------------------------------------------------------------------------ CSBUG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28846 View this thread: http://www.excelforum.com/showthread.php?threadid...

Create a cell with only PART of another cells data
I have a column (D) with the following formatted data 0908-1234 0208-0691 0903-0256 I want to create a cell (column) that ONLY contains the last 4 digits 1234 0691 0256 Can some one please tell me how to accomplish this. Thanks Dave __________ Information from ESET NOD32 Antivirus, version of virus signature database 4443 (20090921) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com Try this: =3DRIGHT(D1,4) assuming the data starts on row 1. If you want it as a number, then you can do this: =3DRIGHT(D1,4)*1 Copy down as required. Hope this helps....

How do I edit directly in a cell
In earlier versions pressing F2 enabled editing directly in a cell. How do I do this in 2007 Office Button > Excel Options > Advanced > Allow editing directly in cells Then F2 will work as desired. -- Gary''s Student - gsnu201001 "Pweter" wrote: > In earlier versions pressing F2 enabled editing directly in a cell. How do I > do this in 2007 The shortcut-key remains same for XL2007 -- Jacob "Pweter" wrote: > In earlier versions pressing F2 enabled editing directly in a cell. How do I > do this in 2007 ...

When entering data into excel spreadsheet cell, the page just jump
I am trying to add data to an existing worksheet this morning. I can click on the clel, but when I try to enter the data, the worksheet "jumps", am unable to enter any data. What have I done wrong? If you are making entries using the numeric keypad, it sounds like you turned your Num Lock off. Try pressing NUM LOCK (there should be alight that comes on above it when you do) & try again. HTH|:>) "jodj" wrote: > I am trying to add data to an existing worksheet this morning. I can click > on the clel, but when I try to enter the data, the worksheet &qu...

Help with date format
Hello ! When I use this function =now()-1 it returns yesterdays date and time less one day in the format that I choose in the format menu. This is my problem. I want a formula like this. "ICSSA Daily Report For "&now()-1 I would like the result to be ICSSA Daily Report For 12-Aug-05 The result that I get is ICSSA Daily Report For 38575.50547 I need to format the date number for the date above but have not been able to figure out how to accomplish this. Many thanks Bob ="ICSSA Daily Report For "&TEXT(now()-1,"dd mmm yyyy hh:mm:ss") adjust the ...

cell color does not show
When I fill a cell with color the color does not show on my worksheet but when I change to print preview I can see the color. How can I fix this? If the high contrast setting is turned on you won't see the fill colour. There's information in the following MSKB article: OFF: Changes to Fill Colour and Fill Pattern Are Not Displayed http://support.microsoft.com/?id=320531 mickid wrote: > When I fill a cell with color the color does not show on > my worksheet but when I change to print preview I can see > the color. How can I fix this? -- Debra Dalgleish Excel FA...

Cell formating
How do I format a cell such that I see a number say 40000000.00 as 4,00,00,000.00 ? -- IndianGuru ------------------------------------------------------------------------ IndianGuru's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27382 View this thread: http://www.excelforum.com/showthread.php?threadid=469097 Click Format then Cells. Select the Number tab. Click on Custom in the category box and in the Type box type #,###.00 "IndianGuru" <IndianGuru.1vngya_1127207106.465@excelforum-nospam.com> wrote in message news:IndianGuru.1vngya_11272...

converting general cells format to text
I'm having a hard time converting already existing general cells into text format. I'm trying to change it into text format so SAS will be able to recognize it. Without the "green indicator", SAS, for some reason, will treat it as a "general" cell even though I've saved it and changed the cells into text. Any suggestions? Sometimes when you type a number into a cell and then change the format to text, it does not "set in" imediately. Hit F2 and then enter on each cell and it should change it for you. Hope this helps. "junkgrrl" wrot...

Locking Named Cells
Lads, I have a lot of named cells in a workbook. Can I lock the name of th cells but allow users to change the data in them? I do not want them to be able to change the cell names becuase o references to them elsewhere in the workbooks. Lia -- wjoc ----------------------------------------------------------------------- wjoc1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1528 View this thread: http://www.excelforum.com/showthread.php?threadid=47473 Are you sure that the users are changing the names? Maybe they're just adding another name for that range...

Compare cells (advanced)
Hi there. I need to find a smart solution for comparing cells in an Excel file. To make a long story short, we usually receive excel sheets with thousands of rows. From a certain data there we make our own extraction from our database and put that in the same sheet, next to the other data. Then we need to compare some data, to make sure that it is correct. Lets say that we are comparing names. The problem is that VERY often the two cells containing the names are not _exactly_ the same. There might for example be one letter misspelled in one of the cells e.g. (Mike Brown/Mike Bruwn) or in one ...

Using a VLookup "type" reference but the result is the CELL not th
I'm using a VLOOKUP to help me select a date range from another work sheet. ie 6/01/04 VLOOKs up a date on another sheet and it's corresponding revenue number. The same is applied to 6/30/04. I'm trying to sum the revenue in the cells (days 6/01-6/30) via the VLOOKUP formula, but the VLOOKUP only returns the value and not the cell location. I need the cell locations to sum the revenue numbers in between. ie. the revenue numbers between B4 and B10. Any help would be greatly appreciated Hi try the following =SUMPRODUCT(--(A1:A100>=DATE(2004,6,1)),--(A1:A100<=DATE(2004,6,1)...

Combo box date format
Access 2003. How to format a date in the text property of a combo box. Setting the format property to dd-mmm-yy or Medium date always returns xx/xx/xx even though the row sources is formatted to dd-mmm-yy with the output of a query. Thanks. -- RobGMiller Are you setting the Format property of the combo box itself on the form? Or of the field to which the combo box is bound? What is the query that you're using as the combo box's Row Source? Post the SQL statement. Which field in that query is the date field? -- Ken Snell <MS ACCESS MVP> "RobGMiller&quo...

How do I insert a dot/check mark in a cell in Excel?
This is very basic! And I am having a problem... I want to insert a dot or check mark in a cell. Where can I find a dot/check mark? I thought they can be easily found, but I can't find them. Can anybody help? See if this helps, Sue: http://www.officearticles.com/misc/symbols_and_characters_in_microsoft_office.htm ************ Anne Troy www.OfficeArticles.com "sue" <sue@discussions.microsoft.com> wrote in message news:93425CB8-2CE6-4277-AA05-38F597DEB1F2@microsoft.com... > This is very basic! And I am having a problem... I want to insert a dot > or > ...

Naming sheets from a cell value
Hi there, Thanks for reading my question. Is it possible to name a sheet from a cell value with in the sheet? i.e. I have 12 sheets, each sheet represents a month and the month value is in cell A1. Can I rename a sheet by refering to cell A1 instead of manually renaming the sheets to January, February etc. Many thanks Tony See http://www.mcgimpsey.com/excel/events/sheetnamefromcell.html In article <127785E3-01B4-49B1-9966-8D79F795EFA4@microsoft.com>, "Tony4X4" <Tony4X4@discussions.microsoft.com> wrote: > Hi there, > Thanks for reading my question. >...

Different beginning cell each time for same macro
I would like my macro to begin in a different cell that I select each time and then have the first step of the macro select the 12 cells immediately to the right of the cell I select each time. Currently my macro runs perfectly except that the macro will only begin in the exact same cell every time the marco is run. This means that the same 13 cells are used by the macro every time. Where as I need the marco to begin in a different cell that I more or less randomly select and then the macro should select the 12 cells immediately to the right of that cell. I know someone out there...

Deleting Cells Option doesn't appear
I have a workbook with several tabs. Each sheet has command buttons and macros -- but the format is identical. I want to delete a few cells (not rows, just cells) On some of the tabs, I can select the cells, Edit / Delete / Shift Up However, on some of the tabs, I can select the cells and I only get the option to "Delete Row". I have compared the Options and other settings, and can't find what would be doing this. Any ideas Nevermind -- I finally found it -- I had a Filter that was on. Apparently, even if the filter is not in the area that you want to delete, it still ...

Entourage formatting
Hello comunity, Im having a litle problem at formating my emails at entourage. I was used to Microsoft Outlook, where i could just bring a .html file previously build and start to use. At entourage i cant find a place to import a .html file and have my emails formated with images and css. Is it possible to build a look for an email in a html file and import at entourage? Thanks, neto On 6/28/07 7:06 AM, in article 1183039592.925648.298180@u2g2000hsc.googlegroups.com, "epa.neto@gmail.com" <epa.neto@gmail.com> wrote: > Im having a litle problem at formating my emails at...

How to specify a fixed cell in a formula
Perhaps I am explaining myself in the wrong termonology but when I drag a cell into multiple cells below it the formula changes cells. How do I make it from changing cells. -- Dave Dave, you need to use a absolute, or mixed reference, form excel help Switch between relative, absolute, and mixed references Select the cell that contains the formula. In the formula bar , select the reference you want to change. Press F4 to toggle through the combinations. The "Changes To" column reflects how a reference type updates if a formula containing the reference is copied two cells down an...