Custom - No, I Mean Really Custom Number Formats

[jqavins]

Excel's so-called custom number formats are actually just configurable from a pretty good but finite palette of options. I want a format that (I'm pretty sure) can't be made from the "custom" formats dialog, and therefore not from the .NumberFormat method either.

The format I want is a minimum number of significant figures, but not fewer than zero decimal places. Yes, I could use scientific notation, but there are time that just doesn't look right. For example:

-3	3142
-2	314
-1	31.4
	3.14
1	0.314
2	0.0314
3	0.00314
4	0.000314
5	0.0000314

<tbody>
</tbody>

The right column is pi, with the decimal point shifted according to the left column, just to have an example to show. The right column is formatted as enough decimal places to provide at least three significant figures and more when the number is at least 1000.

Sure, I could write a little code to set the number of decimal places cell by cell (which is what I did by hand to generate the example) but I'd like to apply a brand new truly custom format to the range. Is there a way to code new formats like this? I'm not asking for the code, as I'm sure I can do that if someone can give me the key starting point, i.e. how to apply such code to all cells in a range as a new format, rather than having to run a macro explicitly whenever the cell values change.

 

[Micron]

Not sure what you mean by code - vba function or formula? I thought I had it with a formula, but the lack of decimals in the minus rows would be an issue for either a formula or a format, methinks. In fact, I don't see how any format could not give you decimals (especially an increasing number of places) in some cases but none in others.
What the formula produced using 3.142 (the PI function produced many of the digits to the right of the decimal).

-3	3142
-2	314.2
-1	31.42
0	3.142
1	0.3142
2	0.03142
3	0.003142
4	0.0003142
5	0.00003142

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[jqavins]

OK, I meant VBA in a function or macro or some form of code block that would be invoked when a brand new format is applied. In informal pseudocode, the following would apply to each cell:

Obtain NumDigs                                   ' The minimum number of significant digits required

FirstDigPos = 1 + int(log(CurrentCell)           ' Position of the first digit left of the decimal point. 3.14 returns 1; 314 returns 3; 0.0314 returns -1. Etc.

if FirstDigPos >= NumDigs
     CellFormat = "#"                             ' When the integer part has enough digits, just show that.

else
     DecPlacesNeeded = NumDigs - FirstDigPos      ' How many decimal places are needed.

' For example, when the cell value is 0.0314159... and NumDigs is 3, FirstDigPos will be -1, giving DecPlacesNeeded = 4.
     CellFormat = concat("0.",REPT("0",DecPlacesNeeded"))

' CellFormat is now "0.0000" so the cell will display as 0.0314

endif

Going from this pseudocode to real VBA is not the problem, I can do that, and I could write a macro to do this cell by cell when invoked. (I'm no Excel macro expert, but I could manage and learn some along the way.) My hope, though, is to use code such as this to create a whole new format code that would be available for use in a format string, or to be able to apply this to a range of cells by some mostly equivalent means.

In other words, it's not the logic or the algorithm or the coding that I need help with, but the Excel hooks that make it possible to integrate. If it is possible.

 

[RoryA]

Short answer - no it is not possible. The format code options are fixed.

 

[jqavins]

I had a "Well, duh!" moment this morning, and partially solved this with conditional formatting. Unlike using a macro to set the number of decimal places it's a PITA to set up, entering a whole bunch of rules one at a time. But, like a built in format option, it can be applied to a range and will update the format live when the value changes. Not perfect, but an improvement over what I have been doing until now.