Convert Grams to Ounces, Ounces to Grams without a conflict
in [General]
|
Prev: Data summary
Next: Error when Saving a Workspace
From: Quin on 8 Apr 2010 12:18 I am creating my own food log in Excel. When I look up different foods some are listed in grams, others in ounces. With two columns, one with a heading of “Ounces” and another with a heading of “Grams” I would like to be able to list one and calculate the other. Both columns can not have formulas that relate to each other, so I am wondering if there is a neat way to accomplish this. 1 gram = 0.0352739619 ounces Quin
From: Mike H on 8 Apr 2010 12:24 Hi, Excel has an inbuilt function to do this. Look for CONVERT in Excel help. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Quin" wrote: > I am creating my own food log in Excel. When I look up different foods some > are listed in grams, others in ounces. With two columns, one with a heading > of “Ounces” and another with a heading of “Grams” I would like to be able to > list one and calculate the other. Both columns can not have formulas that > relate to each other, so I am wondering if there is a neat way to accomplish > this. > > 1 gram = 0.0352739619 ounces > > > Quin >
From: Quin on 8 Apr 2010 13:06 I think you did not quite understand the question I have. No matter if I use a built in function or not, I would not be able to put a formula in both the "Ounces" column and the "Grams" colum for conversion because it would conflict. Quin
From: p45cal on 8 Apr 2010 13:13 Quin;693970 Wrote: > I am creating my own food log in Excel. When I look up different foods some > are listed in grams, others in ounces. With two columns, one with a heading > of “Ounces” and another with a heading of “Grams” I would like to be able to > list one and calculate the other. Both columns can not have formulas that > relate to each other, so I am wondering if there is a neat way to accomplish > this. > > 1 gram = 0.0352739619 ounces > > > Quin > Both columns can not have formulas that relate to each other > Well I tried in Excel 2007 and Excel 2003 and both complained about circular references but both allowed me to push it through: Cell A2 contained: =B2*0.0352739619 and cell B2 contained: =A2/0.0352739619 both copied down. Whichever formula you overwrite with a value, the other formula calculates the conversion. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: 558 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=194035 http://www.thecodecage.com/forumz
From: Chip Pearson on 8 Apr 2010 13:16
Create two defined names, in adjacent cells, with Grams on the left on Ounces on the right. Suppose B3:B20 is the Grams range and C3:C20 is the Ounces range. Right-clcik on the sheet tab and choose View Code. Paste the following code into the code module that appears. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub End If Application.EnableEvents = False If Not Application.Intersect(Target, _ Range("Grams")) Is Nothing Then Target(1, 2).Value = Target.Value / 28.349 ElseIf Not Application.Intersect(Target, _ Range("Ounces")) Is Nothing Then Target(1, 0).Value = Target.Value * 28.349 End If Application.EnableEvents = True End Sub Close the VBA editor and return to Excel. Now, when you enter a number in the Grams cell, the Ounces value appears in the cell to the right. Type something in to the Ounces cell and the Gram value appears to the left. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 8 Apr 2010 09:18:01 -0700, Quin <Quin(a)discussions.microsoft.com> wrote: >I am creating my own food log in Excel. When I look up different foods some >are listed in grams, others in ounces. With two columns, one with a heading >of �Ounces� and another with a heading of �Grams� I would like to be able to >list one and calculate the other. Both columns can not have formulas that >relate to each other, so I am wondering if there is a neat way to accomplish >this. > >1 gram = 0.0352739619 ounces > > >Quin |