Basis point format?

bump

Hi Johnny

I can't think of a way to do it (doesn't mean it doesn't exist of course ) - the best I can think of would involve using a cell to hold your number and an adjacent cell to hold the units (like basis points) and then in any calculation use a lookup table to return the factor that you need to divide the units by:Excel 2002

Thanks Richard, I was hoping to do it without a second table but will have to use something like that

Hi

I can think of 2 solutions but, unfortunately, I cannot recommend them. I'll share them though. I think a soltion like Richard suggests is maybe the answer.

Solution 1 -

I once used a solution for this that worked fine, but it was for a single PC.

I don't recommend that you use it because of the hassle of distributing fonts. I'm just sharing.

When you use a format % what excel does is to divide the value by 100.

I used this to solve the problem in 2 steps:

1- to get the correct numeric value displayed, I used the format like

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

This allows the value to be correctly displayed and does not change the value. You get however 2 % signs that you don't want

2 - to get rid of the annoying % signs in the display I went out of excel. In Windows I used some freeware font editor. I copied the font I used, like Arial to Arial1 and in this new font I replaced the character % by a null character.

Back in excel I used that font in the values with the bp format.

Notice that apart from the problem with the font this solution is very simple and works well.

Solution 2

If you don't have many cells with this format you could use the change event of the worksheet and use a literal format. Also in this case I can't recommend it unless you really have few cells where you need this.

Pedro - that's awesome!

You can avoide the hassle of the font problem if you write the format like this:

%%
#,##0" bp"

where you insert a newline character using Ctrl+j between the two % characters and the first #. Then you just need to set the cell in which you use this format as Wrap Text and it should display 0.01 as 100 bp. I find you also need to increase the height of the cell on my machine by a tiny amount to get it to display properly.

Fascinating solution

Thanks pgc - I like thinking outside the box solutions like the first one! A change event sounds a bit like a hammer to crack a walnut (as if the first one isn't!)

I wonder if I could hack some Workbook open event to load an adjusted font from the network? Then anyone could use it. One to play with when worktimes are a bit dull and the boss is away I think.

I guess if it can't be done easily with a custom number format alone, I'll just write some code to wrap "=(" & ")*1E4" (or 1E2 is the number format is already percent)around the formulae in a selected range and apply <#,##0" bp";[Red]-#,##0" bp"> as a format and attach to a button.

Johnny, see my reply to PGC which details how you can do it with only a number format and Wrap text being applied to the cell. Kudos to Pedro for the %% solution!

Nice one Richard - I did as you said, %%<ctl-j>0" bp", and aligned it to Vertical Bottom and it worked when the height of the rows are increased by 1 pixel. If you resize fonts etc you still need to resize to +1 pixel, but that's asy to automate if need be. I teried to be smart ad use [white] for the % signs and [black] for the rest but Cell Format wasn't having any of it.

Thanks. I think I had posted a similar solution some years ago but I couldn't find it.

Nice workaround, Richard. I hope this problem with the font won't be for long. In Word when you save a file you have the option of embedding the fonts used in the document, I don't see why you couldn't have that option in Excel. Maybe in a future version it will be available. With the font embedded in the workbook there will be no more problem distributing it.