Excel Circular Reference

Simple question. I'm working in Excel 2007, and I want to convert 1000 grams into 1 kilogram when I input 1000g into a particular cell. So I decided to create the if statement below. However there is a problem with this. It's stating that it is a Circular Reference, and leaves me with a 0 value. Is there a simple way to fix this issue in excel?

Here is the if statement I have.

``````=IF(C8 >= 1000,CONVERT(C8,"g","k"),C8)
``````

Tell what you guys think.

-

migrated from stackoverflow.comDec 1 '11 at 0:54

This question came from our site for professional and enthusiast programmers.

Is that formula in cell C8? – SLaks Oct 7 '11 at 2:33

why don't you just format the cell?

The number format property can be accessed in VBA - try this: put 1000 into a cell, then from the immediate window (alt+F11, ctrl+g) type

``````selection.numberformat="0,"" kg"""
``````

does that help?

-
... because CONVERT changes underlying numbers. – brettdj Oct 7 '11 at 9:38
ok well if you want the underlying numbers to be modified, you should have the input (1000) in one cell and the answer in another. – Nick Oct 7 '11 at 14:32

To add some extra information to brettdj's answer, if you want to change a cell when you modify it, you'd probably better use Event procedures such as `Worksheet_Change()`.

For instance, you could do:

``````Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C8")) Is Nothing Then
Target.Value = Application.WorksheetFunction.Convert(Target.Value, "g", "k")
End If
End Sub
``````

Note: I don't have the `Convert` function in Excel 2003, I don't know if it is a UDF or a recent function.

-
• If this formula is in C8

You can't solve this even if you do turn iteration on as I used in Using max/min functions to remember highs/lows in dynamic data in Excel

This is because adding the function will wipe out the value that already exists in C8 (in my linked SO example the formula works as it can look at itself)

• If this formula isn't in C8

Then it should work and you must have a circular reference elsewhere. To find and remove the circular refence look here

-