If 100.0% display 100%
 

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 100.0% display 100%
 

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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jmj713" wrote in message
...
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 100.0% display 100%
 

On Tue, 15 Jan 2008 10:21:01 -0800, jmj713
wrote:

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

If 100.0% display 100%
 

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

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

If 100.0% display 100%
 

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

"Bernard Liengme" wrote:

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

If 100.0% display 100%
 

Use a custom format of:

[=1]0%;0.0%

Regards
Fred

"jmj713" wrote in message
...
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 100.0% display 100%
 

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...

If 100.0% display 100%
 

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/default...NoWebContent=1
(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/default...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
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"jmj713" wrote in message
...
Could you explain a bit, please? What possible errors could there be?

"Bernard Liengme" wrote:

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

If 100.0% display 100%
 

On Tue, 15 Jan 2008 11:50:03 -0800, jmj713
wrote:

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

If 100.0% display 100%
 

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
_______


"jmj713" wrote in message
...
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...

If 100.0% display 100%
 

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

"jmj713" wrote in message
...
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...

If 100.0% display 100%
 

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.

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).

If 100.0% display 100%
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"jmj713" wrote in message
...
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.

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).