Metric system prefix with custom number formatting

[DRSteele]

Can someone please help me?

I am trying to devise custom number formatting to take care of numbers that range from very small to large. I want to use only certain metric prefixes, all while making sure that the output is always the minimum of lead digits (for all but the ones exceeding the top and bottom categories). I also want all the decimal places without trailing zeros.

Like this:

Book1
	A	B	C
3	distance (m)	distance (desired number formatting)
4	0.0000006	.00006 mm
5	0.0005	0.5 mm
6	0.0068	6.8 mm
7	0.0726	7.26 cm
8	0.1552	15.52 cm
9	1.043	1.043 m
10	17.896	17.896 m
11	289.532	289.532 m
12	3500.28	3.50028 km
13	96875620.2	‪96,875.6202‬ km
14
15	unit	acceptable	power
16	mm	yes	0.001
17	cm	yes	0.01
18	dm	no	0.1
19	m	yes	1
20	dam	no	10
21	hm	no	100
22	km	yes	1000
Sheet59

 

[Trevor_S]

You can build conditions into a number format, so that different "sections" separated by semicolons each contain a condition and the format to apply (rather than the standard sections of positive, negative, zero and text) - see Microsoft website at: Control settings in the Format Cells dialog box - Office

But what you're trying to do requires a calculation as well as a format - I don't think that's possible in a custom number format. Would a user defined function be ok?

 

[DRSteele]

Thank you for your reply, Trevor.

I think I figured it out. It wasn't easy, but I relied on your suggestion of Conditional Formatting, and combined it with Chandoo's novel solution using commas and percents to divide by 1000s and multiply by 10s. The number formatting is what goes into the CondFrmt format box. Note that the <CtrlJ> does not get typed in by rather gets invoked. [Notes: The numbers below in E4:E13 do NOT show that they are conditionally formatted but they are in my spreadsheet. The "Cell format" box below says TEXT buy it should be number formatting.]

It's awkward but it works, I think. Can you please try it out and we'll see if I'm onto something?

Book1
	E	F
3	wrap text	Custom Number Formats (Multiply & Divide by any Power of 10)
4	0.0000006
5	0.0005
6	0.0068
7	0.0726
8	0.1552
9	1.043
10	17.896
11	289.532
12	3500.28
13	96875620.2
14
15	number format
16	#,##0.####" mm",<CtrlJ>%%%
17	#,##0.####" cm"<CtrlJ>%
18
19	#,##0.####" m"
20
21
22	#,##0.####," km"
Sheet59

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4:E13	Expression	=E4<0.01	text	YES
E4:E13	Expression	=E4<1	text	YES
E4:E13	Expression	=E4<1000	text	YES
E4:E13	Expression	=E4>=1000	text	NO

 

[Tom.Jones]

Hi,

You can use excel function CONVERT.
=CONVERT(A1,"m","mm")

 

[Trevor_S]

I can't see a post by Chandoo? But I've tried the number formats suggested and can't get them to work. As you would expect, the "%" character multiplies by 100 - and two "%" characters actually multiplies by 10,000! But that won't allow you to do multiples of 10 or 1,000 or divide - plus the % character(s) are visible in the formatted cell. From what I've been able to find on the internet, Ctrl + J should give some form of line feed (a bit like Ctrl + Enter while inputting into a cell) Presumably the idea is to move the "%" character(s) down so that (unless the row height is increased) they won't be visible. Not ideal if the row height is set to auto-fit, and I haven't been able to get it to display the line feed anyway.

My suggestion (if you can work out how to make the calculation bit work) wasn't to use conditional formatting. It was to build the conditions into the custom number format sections, instead of using the "default" sections of positive; negative; zero; text. For example, a custom number format of:
[>=1000]#,##0.###" km";[>=1]#,##0.###" m";[>=0.01]#,##0.###" cm";#,##0.####" mm"
will convert as follows:

Unformatted number	Displays as
1000	1,000 km
1	1 m
.01	0.01 cm
.001	0.001 mm

So this custom number format displays the correct unit depending on the cell value, but won't do the multiplication on the numeric element.


If you can't get the custom number format to do the maths, I think you're down to using a formula. If you don't mind a nested "if" formula on the spreadsheet, try:
=IF(E4>=1000,E4/1000&" km",IF(E4>=1,E4&" m",IF(E4>=0.01,E4*100&" cm",E4*1000&" mm"))) where E4 contains the value in metres.

There is also the CONVERT function provided by Tom, but you'd need some way of getting it to display the units.

 

[DRSteele]

The Chandoo link is in F3 above - just click on the green underlined stufff.

The % multiplies by 100 and the , divides by 1000. So %%% is 10000000 which gets divided by a , of 1000 for a net multiple of 1000. Etc.

To get the line feed to work, the cell is formatted wrap text.

 

[Trevor_S]

Ok, found it now! I thought you meant a post to this thread!

Haven't got access to the PC at the moment, and you can't set custom number formats on the phone app. But if Chandoo's theory works, then you should be able to combine it with mine - so you create a single custom number format something like:
[>=1000]0.###," km";[>=1]0.#" m";[>=0.01]#,#.#" cm"<CtrlJ>%;#,#.#," mm"<CtrlJ>%%%

I've put the <CtrlJ> after the unit so that the unit still appears in the top line.

 

[DRSteele]

Thank you for the suggestion, but I can't seem to make it work. The proposed CNM does not invoke.

 

[Rick Rothstein]

I do not think you can do what you want with normal Cell Formatting; however, there is a VBA solution. The following Change event code (set for range A1:A100... change as needed) will retain the cell's entered value but display it as you indicated you wanted in Message #1...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
    If Target.Value < 0.01 Then
      Target.NumberFormat = """" & Target.Value * 1000 & " mm"""
    ElseIf Target.Value < 1 Then
      Target.NumberFormat = """" & Target.Value * 100 & " cm"""
    ElseIf Target.Value < 1000 Then
      Target.NumberFormat = """" & Target.Value & " m"""
    Else
      Target.NumberFormat = """" & Target.Value / 1000 & " km"""
    End If
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[DRSteele]

Thanks for you reply, Rick. In my current project I can't have VBA. My post#3 works okay.