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

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> >

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> >

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

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

Quin;693970 Wrote:

I am creating my own food log in Excel. When I look up different foods

some

heading

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.yqcomputer.com/

http://www.yqcomputer.com/

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

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

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

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

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

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

Quin;694326 Wrote:

Chip,

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

--

p45cal

*p45cal*

------------------------------------------------------------------------

p45cal's Profile: 558

View this thread: http://www.yqcomputer.com/

http://www.yqcomputer.com/

2. how do you take an answer in a cell and converting it from grams .

4. converting pounds and ounces to kilograms

5. How do I convert cubic centimeters to ounces in Excel?

6. How do I convert kilograms to ounces in excel?

10. How to gram awk's regexp submatches?

11. Gram-Schmidt orthogonalization process

14. No spell-gram check in prefs

15. Gram-schmidt

8 post • Page:**1** of **1**