If 100.0% display 100%

[jmj713]

I have a sheet where several numbers are compared and the difference is
displayed in percentage. For instance, if the first number is 1 and the
second number is 2, then the difference is +1 or 100%. The numbers I have are
much more complex and varied, so my percentage is actually set to display 1
decimal place. But there are some occasions when the percentage is a whole
number, like 100%, and I don't want that trailing zero there. Is that
possible to remove somehow, leaving numbers that are not whole with their .7
or .2 decimals?

 

[Bernard Liengme]

Numbers in A1 and B1. my percentage is (B1-A1)/A1; Adjust to suit your needs

=IF(MOD((B1-A1)/A1*100,1)<0.00000001,TEXT((B1-A1)/A1,"#%"),TEXT((B1-A1)/A1,"#.#%"))

Note I do not check MOD for zero because of the possibility of round off
errors as with 2 and 4.56
best wishes

 

[Ron Rosenfeld]

I have a sheet where several numbers are compared and the difference is
displayed in percentage. For instance, if the first number is 1 and the
second number is 2, then the difference is +1 or 100%. The numbers I have are
much more complex and varied, so my percentage is actually set to display 1
decimal place. But there are some occasions when the percentage is a whole
number, like 100%, and I don't want that trailing zero there. Is that
possible to remove somehow, leaving numbers that are not whole with their .7
or .2 decimals?

If you don't want to use a helper column to display the results, you could use
an event triggered macro to change the formatting "on the fly".

Try the code below. You may need to modify it.

To enter the code, right-click on the sheet tab, then paste the code below into
the window that opens. Change the value of "A" to represent the range where
you might have your percentage results displayed.

===============================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, C As Range
Set A = [A1:A100] 'range containing percents
For Each C In A
With C
If InStr(1, .NumberFormat, "%") > 0 Then
If Int(.Value * 100) = .Value * 100 Then
.NumberFormat = "0%"
Else
.NumberFormat = "0.0%"
End If
End If
End With
Next C
End Sub
=====================================
--ron

 

[jmj713]

Thanks, but I've never worked with macros. I'll try this, but is this just
for 100 or any whole number?

 

[jmj713]

Could you explain a bit, please? What possible errors could there be?

 

[Fred Smith]

Use a custom format of:

[>=1]0%;0.0%

Regards
Fred

 

[jmj713]

Use a custom format of:

[>=1]0%;0.0%

I just applied this to a cell which I have as 1.0% and it's still got the
..0...

 

[Bernard Liengme]

Because computers store numbers in binary with a finite number of digits, we
sometimes get values like 0.000000000000012 when exactly zero is expected.
See

INFO: Visual Basic and Arithmetic Precision
http://support.microsoft.com/defaul...port/kb/articles/Q279/7/55.ASP&NoWebContent=1
(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/defaul...pport/kb/articles/Q42/9/80.ASP&NoWebContent=1
http://support.microsoft.com/kb/78113/en-us
What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm
best wishes

 

[Ron Rosenfeld]

Thanks, but I've never worked with macros. I'll try this, but is this just
for 100 or any whole number?

Huh?

I thought you wanted a format of 0% for any percentage value that would
otherwise be expressed as n.0%, and a format of 0.0% for the others.

So the macro is written to do that. If you have other requirements, please
re-state them.

The macro should be modified to handle certain error conditions, so I would use
this:

======================================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Range, C As Range
Set A = [A1:A100] 'range containing percents
For Each C In A
With C
If InStr(1, .NumberFormat, "%") > 0 And _
IsNumeric(.Value) Then
If Int(.Value * 100) = .Value * 100 Then
.NumberFormat = "0%"
Else
.NumberFormat = "0.0%"
End If
End If
End With
Next C
End Sub
================================
--ron

 

[Jon Peltier]

In your initial post, you said 100% is a whole number, which it is (100%=1),
whereas 1% is a small fraction (=0.01 or 1/100).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Use a custom format of:

[>=1]0%;0.0%

I just applied this to a cell which I have as 1.0% and it's still got the
.0...

 

[Fred Smith]

That's because you asked for 1% to be displayed as 1.0%. You asked for 100%
to be displayed as 100%. The format will do that.

Regards,
Fred

Use a custom format of:

[>=1]0%;0.0%

I just applied this to a cell which I have as 1.0% and it's still got the
.0...

 

[jmj713]

Perhaps I wasn't clear, I'm sorry. I wanted any whole number percentage
(1.0%, 27.0%, 78.0%, 100.0%) to drop the .0 decimal.

 

[Jon Peltier]

This became clear to me after reading more of the interchanges. It cannot be
done with a number format, so I decided to let anyone who wanted to write
formulas or VBA get the glory.

- Jon