Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 stackoverflow.com Dec 1 '11 at 0:54

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

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

3 Answers

  • 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
add comment

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

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

Your Answer

 
discard

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