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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bkdelong@pobox.com - 13 May 2008 23:07 GMT

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?

Reply to this Message

Dave - 13 May 2008 23:53 GMT

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.

Reply to this Message

BoniM - 14 May 2008 02:46 GMT

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

> Trying to get a column to auto-format numbers based on value - I've
> done some Google searching, checked through newsgroup archives and
[quoted text clipped - 6 lines]
>
> Is this even possible or should I do everything in millions?

Reply to this Message