Convert Grams to Ounces, Ounces to Grams without a conflict

Convert Grams to Ounces, Ounces to Grams without a conflict

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 uncesand another with a heading of ramsI 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

Convert Grams to Ounces, Ounces to Grams without a conflict

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.

> of uncesand another with a heading of ramsI 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> >

Convert Grams to Ounces, Ounces to Grams without a conflict

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

Convert Grams to Ounces, Ounces to Grams without a conflict

Quin;693970 Wrote:
I am creating my own food log in Excel. When I look up different foods
some
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

http://www.yqcomputer.com/

Convert Grams to Ounces, Ounces to Grams without a conflict

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

>of uncesand another with a heading of ramsI 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

Convert Grams to Ounces, Ounces to Grams without a conflict

I would use multiple columns.

The first would be for the Qty -- no units here.
The second would be the units (g or o, or Grams or Ounces, or whatever you
want).
The third would translate/copy to grams.
The fourth would translate/copy to ounces.

Qty Unit Grams Ounces
3 g 3
10 o 0.352739619

The formula in C2: =IF(B2="g",A2,A2/0.0352739619)
The formula in D2: =IF(B2="o",A2,A2*0.0352739619)

> of uncesand another with a heading of ramsI 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

--

Dave Peterson

Convert Grams to Ounces, Ounces to Grams without a conflict

Chip,

Your solution is exactly what I wanted. I tried it out and it worked
perfectly. I will want to come back to this post to see if I can understand
the VBA. What a wonderful talent to have, writing VBA to improve performance
of a work sheet. Thanks for taking the time to write the code.

Dave, your solution was also appreciated and would work well for someone
that gets stuck implementing the VBA.

Quin

Convert Grams to Ounces, Ounces to Grams without a conflict

Quin;694326 Wrote:
Chip,

Oh, well, last and least.. oh no wait, not even last!

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: 558