Custom number formatting in Excel

[jaqcues]

Hi there,

Im trying to display some tricky % number formatting and have hit a dead end. Just wondering if it's possible to display % under the following criteria

Over 100%: +xxx%
0-100%: +xx.x%
-100%-0%: [red]-xx.x%
Under -100%:[red]-xxx%

The key is basically to retain the +/-, the black/red and the 3 significant figures throughout. I already have conditional formatting on the cell which uses green/red icons to highlight growth/decline.

Keen on any advice! Ideally this will be possible under number formatting alone; I can achieve the +/- and red easily, or alternatively the 3 sig figs with [=0]"N/A";[<1]##.0%;###%.

 

[mole999]

not sure it works 100% of the time but in custom format after the plan try "b" and that might add b to the end of your display

"+/-" General "b"

 

[Yongle]

How about this custom format

[=0]"N/A";[Red][<0]-##.0%;+###%.

Ignore this - I missed something !!

 

[Fazza]

I don't trust my memory on this, but IIRC one is limited to three number ranges. So four as desired isn't possible.

What about just using some nested IF with TEXT to return a string result? So the result would be text & can't be added up. Can maybe handle this using two fields - one numeric & one text. If that makes sense

 

[Yongle]

This works

1 apply custom number format as in post#1
Range Number Custom Format: [=0]"N/A";[<1]##.0%;###%

2 add NEW CF rule (to insert + before positive numbers)
if value > 0
Number Format \ custom format : [<1]+##.0%;+###%

3. add NEW CF rule (to amend negative number font colour to red)
if value < 0
Format \ Font \ and set Colour selection to: RED

Althouigh not visible in the extract below, my worksheet also shows the Red \ Green CF icons in each cell

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

+494%.​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

-323.3%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

-322.1%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

+23.5%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

-93.6%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

-310.5%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

-257.0%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

+45.7%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

-245.3%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

+69.1%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]

+334%.​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td]

-196.8%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td]

+198%.​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td]

-242.0%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td]

-160.7%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td]

-60.9%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]

+155%.​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

19​

[/td][td]

-56.4%​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

20​

[/td][td]

+243%.​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

21​

[/td][td]

-18.4%​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[jaqcues]

Hi all,

Thanks for your replies!

not sure it works 100% of the time but in custom format after the plan try "b" and that might add b to the end of your display

"+/-" General "b"

How about this custom format

[=0]"N/A";[Red][<0]-##.0%;+###%.

Ignore this - I missed something !!

Thanks anyway!

I don't trust my memory on this, but IIRC one is limited to three number ranges. So four as desired isn't possible.

What about just using some nested IF with TEXT to return a string result? So the result would be text & can't be added up. Can maybe handle this using two fields - one numeric & one text. If that makes sense

This strategy would work, the only issue is that I have basically made dummy slides which are refreshable each month and linked to Powerpoint, so there are space constraints and also the icon would be a bit off centre (if i were to use the value cell as an icon-only CF).

This works

1 apply custom number format as in post#1
Range Number Custom Format: [=0]"N/A";[<1]##.0%;###%

2 add NEW CF rule (to insert + before positive numbers)
if value > 0
Number Format \ custom format : [<1]+##.0%;+###%

3. add NEW CF rule (to amend negative number font colour to red)
if value < 0
Format \ Font \ and set Colour selection to: RED

Althouigh not visible in the extract below, my worksheet also shows the Red \ Green CF icons in each cell

Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]E[/TH]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

2​

[/TD]
[TD]

+494%.​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

3​

[/TD]
[TD]

-323.3%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

4​

[/TD]
[TD]

-322.1%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

5​

[/TD]
[TD]

+23.5%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

6​

[/TD]
[TD]

-93.6%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

7​

[/TD]
[TD]

-310.5%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

8​

[/TD]
[TD]

-257.0%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

9​

[/TD]
[TD]

+45.7%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

10​

[/TD]
[TD]

-245.3%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

11​

[/TD]
[TD]

+69.1%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

12​

[/TD]
[TD]

+334%.​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

13​

[/TD]
[TD]

-196.8%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

14​

[/TD]
[TD]

+198%.​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

15​

[/TD]
[TD]

-242.0%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

16​

[/TD]
[TD]

-160.7%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

17​

[/TD]
[TD]

-60.9%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

18​

[/TD]
[TD]

+155%.​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

19​

[/TD]
[TD]

-56.4%​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

20​

[/TD]
[TD]

+243%.​

[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]

21​

[/TD]
[TD]

-18.4%​

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet1[/TD]
[/TR]
</tbody>[/TABLE]

I was half way through typing that this was 99% there, then I tried 1 additional tweak and it is perfect (was just values under 1%, showing as ".x%" instead of "0.x%", mainly personal preference), the difference was in step 1.

1 apply custom number format as in post#1
Range Number Custom Format: [=0]"N/A";[<1]#0.0%;###%

2 add NEW CF rule (to insert + before positive numbers)
if value > 0
Number Format \ custom format : [<1]+##.0%;+###%

3. add NEW CF rule (to amend negative number font colour to red)
if value < 0
Format \ Font \ and set Colour selection to: RED

Amazing, thanks for your help!

 

[jaqcues]

Whoops, had to tweak 2nd and 3rd point

1 apply custom number format as in post#1
Range Number Custom Format: [=0]"N/A";[<1]#0.0%;###%

2 add NEW CF rule (to insert + before positive numbers)
if value > 0
Number Format \ custom format : [<1]+#0.0%;+###%

3. add NEW CF rule (to amend negative number font colour to red)
if value < 0
Number Format \ custom format : [>-1]#0.0%;###%
Format \ Font \ and set Colour selection to: RED

Boomfa