Formula & Result in same cell

[bobw19361101]

Is it possible to use this formula in a cell and have it's result appear in the same cell?
Cell A1 formula =if(a1>0,a1/12,a1)
I know that it is a circular ref and I have changed the int to 1.
Problem: When I insert a value into a1, it wipes out the formula.
Any help is appreciated.

[pwhooftman]

No, it's not.

How would you get a value into the cell?. As you say, it wipes the formula.

And if a value could be entered, how do you stop the formula from repeatedly calculating 1/12th of its own value?

What are you trying to achieve?

[bobw19361101]

I have a yearly figure in cell a1. I do not need that number but I do need the monthly number. I have info in many columns and was trying to save a column.
I can stop the formula from continiously refiguring by setting under tools, options, calculate, the iterm to 1. Thus it will only calculate once.

[Carim]

Hi,

The only way is to use the following event macro ...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target.Value = Target.Value / 12
Application.EnableEvents = True
End Sub

[MrShorty]

At which point one has to ask himself which is worse: adding a column or adding an event macro. Only you can make that kind of decision.

Personally, I would prefer to add a column before I added an event macro.

[Dav]

i agree with Mr shorty but, but why dont the other formulas that reference the cell just divide the figure by 12.

a global replace of a1 with a1/12 may work depending on the $ and any a11 or 12s etc

or perhaps dividing the other answers by 12 depending what the formula does

regards

Dav

[bobw19361101]

Thanks for all the help. I guess the easiest thing is to use another column. May try to write the event macro as I am always trying to learn new things. Thanks once again for sharing your knowledge.

Bob