Convert Grams to Ounces, Ounces to Grams without a conflict

[Quin]

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

 

[Mike H]

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]

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

 

[Chip Pearson]

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

 

[Dave Peterson]

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)

 

[Quin]

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