Custom Format as Currency with If/Then ($1B or $500M)

[bkdelong]

Trying to get a column to auto-format numbers based on value - I've
done some Google searching, checked through newsgroup archives and
must be looking in the wrong place.

I have a variety of numbers from $146.0B to $.0093B using a Custom
Format of $#.0####,,,"B". Ideally, the "$.0093B" should be rendering
as $9.3M but I'm not sure how to do an if/then statement or some sort
of tweak to allow this to happen.

Is this even possible or should I do everything in millions?

 

[Dave]

Hi,
You could do this with an event macro.
Something like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect target thingy < 500000 then ActiveCell format is millions
If Intersect target thingy > 500000 then ActiveCell format is billions
End Sub

Regards - Dave.

 

[BoniM]

You can do this with conditional formatting if you have Excel 2007:
Conditional formatting - New Rule - Use a formula to determine which cells
to format
=D2>=1000000
Format - number tab - set custom format - $#.0####,,"M" - OK
then again...
Conditional formatting - New Rule - Use a formula to determine which cells
to format
=D2>=1000000000
Format - number tab - set custom format - $#.0####,,,"B" - OK

If it formats them all as millions - Conditional formatting - manage rules -
and reverse the order...