Custom number format (display result divided by 100)

[Pete Wright]

Hi!

I'm trying to format a number as in this tutorial.
Unfortunately it is not working, actually the comma "," isn't working.

What I would like to do is some basic math on the result without touching the formula.

For example:
number 123 [mm] > display 12.3 [cm]
number 12345 [mm²] > display 123.5 [cm²]
number 123456789 [mm⁴] > display 12345 [cm⁴]

For better understanding I have attached an simple Excel file.

Any help appreciated.
Thanks!

 

[Excel Wizard]

Insert a new line before % by press Ctrl+J
View attachment 72628

 

[Pete Wright]

Insert a new line before % by press Ctrl+J
View attachment 72628

Doesn't work :-(

Screen Capture

 

[Peter Bartholomew]

I couldn't get the area to work with its %%
Otherwise had fun with the naming convention.

 

[Pete Wright]

I couldn't get the area to work with its %%
Otherwise had fun with the naming convention.
View attachment 72635

WOW!!! Super nice!
I see you have used Array Formulas on some cells. I don't know much about that, so I'll go through some tutorials on Array Functions right away...

Many thanks for your effords!

__________
EDIT: Didn't know that subscript characters are allowed in Names as well. I thought only letters, numbers and a very limited amount of special characters like period or comma. Good to know, makes reading the formulas much easier. Thanks for that, too!

 

[Peter Bartholomew]

If you use the Unicode character set for names there is the problem that they are hard to access from the keyboard. A workable strategy is either to use F3 to bring up the Paste Name dialogue box or to start with the bit you can type and then use tab to select the name from IntelliSense.

The best I could achieve with the formatting was to ignore the Ctrl J trick and simply use spaces to align the values spacing them away from the right margin. A white rectangle hides the % and %%.



Something else I did about 10 years ago was write some VBA to create dynamic drawings that adjusted to changes of input parameter using freeform shapes.

 

[Pete Wright]

Something else I did about 10 years ago was write some VBA to create dynamic drawings that adjusted to changes of input parameter using freeform shapes.

View attachment 72636View attachment 72639

Very nice! I do lots of stuff with Excel and VBA, but when it comes to more complicated calculations, I write them in JavaScript and open the calculations in my browser