Special numeric formats

[Ljvs]

In addition to the +;0;-;text set of numeric formats, I understand one can scale the presentation of a number. However, I don't find anything about this in "help". What I want is a way to display a wide range of values such as .01, 2, 3000 as, for example, 10mg, 2g, 3kg. Is this possible?

[Mark W.]

On 2002-05-13 06:55, Ljvs wrote:
What I want is a way to display a wide range of values such as .01, 2, 3000 as, for example, 10mg, 2g, 3kg. Is this possible?

Using built-in formatting you can display...

2 using 0"g" to get 2g
3000 using 0,"kg" to get 3kg

...but, not 0.01 to get 10mg

[Damon Ostrander]

Hi Ljvs,

Here is a user-defined function (UDF) that does this:

Function FormatGms(Grams As Double) As String
Const Fmt = "0"
Select Case Grams
Case 0
FormatGms = "0g"
Case Is > 1000
FormatGms = Format(Grams / 1000, Fmt) & "Kg"
Case Is > 1
FormatGms = Format(Grams, Fmt) & "g"
Case Is > 0.001
FormatGms = Format(Grams * 1000, Fmt) & "mg"
End Select
End Function

Just put this code in a macro module, and call it like:

=FormatGms(A1)

[Mark W.]

Damon, your UDF creates a text value rather than formatting... right?

[Ljvs]

Many thanks to Mark W. His response got me going. If I store the smallest unit (milligrams in the example), then the following format will usefully compress a very wide range of values:

[<1000] 0" mg";[>=1000000] #,#00,," kg"; 0," g "

The extra spaces in " g " keep the units pretty well lined up.

[Mark W.]

On 2002-05-13 11:33, Ljvs wrote:
Many thanks to Mark W. His response got me going. If I store the smallest unit (milligrams in the example), then the following format will usefully compress a very wide range of values:

[<1000] 0" mg";[>=1000000] #,#00,," kg"; 0," g "

The extra spaces in " g " keep the units pretty well lined up.

Hmmmm... I'd be careful there...

If you apply this format to a cell containing 0.01 and expect to get 10 mg... you won't! You'll get 0 mg instead!

The problem is that formatting never alters the underlying value; however, in the case of 0.01g to 10mg you need to move the decimal place (by multiplying by 1000). Fomatting won't do this for you.

[Damon Ostrander]

Mark,

Yes, my UDF displays the result as text. Thus one can't use the underlying value for calculation--just for display. But the value in A1 can be use for calculation.

Using a UDF like this to display the result as text is definitely not the ideal, but sometimes is the only way to get the desired formatting.

Damon

[Mark W.]

On 2002-05-13 15:14, Damon Ostrander wrote:
Mark,

Yes, my UDF displays the result as text. Thus one can't use the underlying value for calculation--just for display. But the value in A1 can be use for calculation.

Using a UDF like this to display the result as text is definitely not the ideal, but sometimes is the only way to get the desired formatting.

Damon

The reason why I asked... Why couldn't a macro change the value's scale and then apply the appropriate custom format. For example, change 0.01 to 10 and format as 0"mg".

[Damon Ostrander]

Hi again Mark,

It's been a while, but just to show you that I haven't forgotten--

Regarding writing a macro that would use cell formatting, I can't see a way to do that because I know of no way to display the value as milligrams when it is less than one. Unfortunately, Excel formatting only provides a way to scale numbers moving the decimal place to the left, not to the right. So even a macro can't display the number in milligrams without modifying the underlying cell value.