Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

Thanks in advance.

share|improve this question

migrated from Dec 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
  • 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

share|improve this answer

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().

See Chip Pearson's website on this topic.

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.

share|improve this answer

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?

share|improve this answer
... 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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.