creating a ppm number format

[MrShorty]

posted this a couple weeks ago (http://www.excelforum.com/showthread.php?t=363252) and didn't get any responses, so I'm assuming that there isn't a built in "ppm" number format as described.

For those who didn't see the previous post, I want something like a "," only going the other direction. Where a "," in a number format code shifts the decimal in the display to the left three places, I want a code that will shift the code to the right three places. The effect would be to display a number such as 0.000011 as 11.

Question now is, does anyone have experience using VBA to create "custom" custom number format codes like this?

[bhofsetz]

Is there are reason you can't simply multiply your value by a million and paste special as values back into the same cells?

[MrShorty]

That's kind of what I do now, and it works ok.

When I use the comma number format, 12,000,000 can be displayed as 12. 12 is just the display, the spreadsheet "knows" that the real value stored in that cell is 12,000,000. Similarly, if I have a cell with the value 0.12, I can use the percent format to display 12%. The cells value remains 0.12. If I multiply these two cells together, I 12,000,000*.12=1,440,000, which would be the correct answer. I don't get 12*12=144, because the spreadsheet knows what the actual value is associated with the displayed numbers.

I would like to be able to do the same thing with a number like 11e-6. I would like to be able to display that value as 11, but have the spreadsheet remember that the displayed 11 means 11e-6.

As it works now (like you've suggested) I can multiply by 1e6 to get it to display as 11, but I then have to divide by 1e6 in further calculations that use that cell to retain it's real value.

Does that make sense? Am I asking for more than Excel can give me?