converting annual $ to monthly $ within one cell

[Judi]

WinXP, Excel 2003

Does anyone know a formula for this situation: I type $1200 (annual LP gas)
in a cell and as soon as I leave the cell, the figure automatically changes
to $100 (monthly LP gas). I have many columns of figures where I have to
convert the annual figures to monthly figures.

I know that I could have the annual in one column and the monthly in another
column, but there isn't room on this report for that.

Judi

 

[Peo Sjoblom]

You could put 12 in an empty cell, copy it, select all values you need to
convert and do edit>paste special and divide


--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Judi]

That works.......but is there a way to hide the 12...don't want that figure
just hanging out for no reason on the page.

Thanks for your help.

 

[Vitalie Ciobanu]

To hide, just make its font color white or the same as background

 

[Michael Bednarek]

That works.......but is there a way to hide the 12...don't want that figure
just hanging out for no reason on the page.

But then you couldn't select and copy it, which is necessary for for the
Paste Special... operation.

My own method in these circumstances is to enter =1200/12 into the cell.

Alternatively, you could write a macro which does the division in those
cells. The third option, writing a macro which divides each cell as it
is entered, might get in your way if you ever want to modify values. The
code for such a macro would be:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Target = Target / 12
Application.EnableEvents = True
End Sub
and goes into the sheet's code, not into a general Module.

 

[Player1]

Or you could hide the "12" on a different sheet (tabs at the bottom of
the spreadhseet).

Player1 - specialising in low tech solutions: from spaces and hard
returns in word documents to tables in html layout formatting....

 

[Pete_UK]

Once you have done the paste special and converted the values to
monthly ones, you don't need the 12 so you can just delete it.

Pete