Basis point format?

[Johnny C]

In the banking world, a lot of numbers are quoted as 'basis points' - 1/100th or 1%.

it's easy to format whole numbers as "bp", but it's not ideal as you need to convert the decimal number by multiplying by 10,000 to geta whole number and then have a format as

#,##0" bp";[Red]-#,##0" bp"

Is there a way to automatically scale a number using a format, so that 0.0065 can be formatted as bp? I've large tables of interest margins that need formatting as bp but will be used in revenue calculations, and don't want to replicate them needlessly

I know you can do thousands and millions, but can't see a way to do it in single magnitudes

 

[pgc01]

Hi

I tried Richard's idea but with the %% at the end, like

#,##0" bp"
%%

with the ^J before the %%. It seems that this way you don't need to adjust the height of the cell. I just turned Wrap text on.

 

[ag73]

Have been trying to work out how to do this for a while, and just found this now ... fantastic solution. Well done all. Cheers, AG