Large worksheet formatting: change % formats only?

[Bill Weylock]

I am looking at a worksheet with about 6000 rows of survey cross-tabs.

Each response is provided in both actual count (# of people saying ³yes²)
and percentage against the base of everyone answering the question.

The ³count² numbers are integers in General format.

The percentages are formatted as percents with 2 decimal places.

All of that is fine, except that the tables are littered with things like
100.00%, 82.00%, 75.60%.

It would be far easier to read the tables if I could format percentages to
display two decimal places only if the last two places were NOT 00 and to
display one decimal place only if the last place was not 0. The results
(sorry for the detail if this has been obvious since the first word) would
then look like 100%, 82%, and 75.6%.

If all numbers were percentages, I think I might be able to figure a way to
handle this in number formatting.

Since half of them need to be displayed as integers, though, I¹m stuck.

Any ideas??

Actually, if you¹re fairly sure there is no solution, that would be a help
in itself!

Thanks!


Best,


- Bill




Panther 10.3.6
Office 2004
Windows XP Pro SP2
Office 2003

 

[JE McGimpsey]

It would be far easier to read the tables if I could format percentages to
display two decimal places only if the last two places were NOT 00 and to
display one decimal place only if the last place was not 0. The results
(sorry for the detail if this has been obvious since the first word) would
then look like 100%, 82%, and 75.6%.

Kind of...

Format/Cells/Number/Custom 0.??%

will return

100.00% 100. %
75.60% 75.6 %
1.23% 1.23%

And choosing Format/Cells/Number/Custom 0.##%

will return

100.00% 100.%
75.60% 75.6%
1.23% 1.23%

or, a little more complicated:

=VALUE(TEXT(A1,LEFT("0.00",1 + 2*(MOD(ROUND(A1*100,2),1)>0) +
(MOD(ROUND(A1*1000,1),1)>0)) & "%"))


100.00% 100%
75.60% 75.6%
1.23% 1.23%

 

[Bill Weylock]

Thanks.

I don¹t think the custom cell formats work for me, though. As I said, I
might be able to handle it myself if all of the numbers were supposed to be
expressed as percents. Not the case, though. I need something that will
operate only on numbers already formatted as percentages.

This is what adjacent rows look like (18 columns total)

129 30 21 28 19
21.50% 10.00% 21.00% 28.00% 19.00%

The first two formatting options will change all of the numbers to % format.
Manually selecting rows containing percents and applying formatting is not
an option with 5200 alternating integer/percentage rows. Also, I would need
to figure a way to get rid of the hanging decimal point.

The formula results are exactly what I want. When I try to apply it,
however, it produces a cell not formatted as a percent. I can get the result
I want only by manually applying a % format with the number of places I
need.

Not sure I can figure a way to apply it to my situation, assuming the result
discrepancy is my fault or would require only a minor tweak. I am guessing I
somehow duplicate the tables on same rows in adjacent columns and use the
formula to fill the percentage rows based on data in the original tables?

I could probably figure a way to do that, but again, I still don¹t see how
to exclude the rows that currently (and need to) display integers.

Any other thoughts? Did I misinterpret how to apply the formula?

Thanks again!


Best,


- Bill




Kind of...

Format/Cells/Number/Custom 0.??%

will return

100.00% 100. %
75.60% 75.6 %
1.23% 1.23%

And choosing Format/Cells/Number/Custom 0.##%

will return

100.00% 100.%
75.60% 75.6%
1.23% 1.23%

or, a little more complicated:

=VALUE(TEXT(A1,LEFT("0.00",1 + 2*(MOD(ROUND(A1*100,2),1)>0) +
(MOD(ROUND(A1*1000,1),1)>0)) & "%"))


100.00% 100%
75.60% 75.6%
1.23% 1.23%[/QUOTE]




Panther 10.3.6
Office 2004
Windows XP Pro SP2
Office 2003

 

[JE McGimpsey]

The formula results are exactly what I want. When I try to apply it,
however, it produces a cell not formatted as a percent. I can get the result
I want only by manually applying a % format with the number of places I
need.

Whoops - you're exactly right - I'd applied one of the custom formats
first. Remove the VALUE(...) wrapper. It produces text, but can be
coerced to values if needed in subsequent calculations.

 

[Bill Weylock]

Thanks, but what about my integers? They will be converted to percents,
right?

If not, bless you! If so, this really does not help me, as welcome as it is.

Any way to apply this ONLY to cells formatted as percents?


Best,


- Bill




Whoops - you're exactly right - I'd applied one of the custom formats
first. Remove the VALUE(...) wrapper. It produces text, but can be
coerced to values if needed in subsequent calculations.[/QUOTE]




Panther 10.3.6
Office 2004
Windows XP Pro SP2
Office 2003

 

[J Laroche]

JE McGimpsey wrote on 2005/01/04 15:01:

Whoops - you're exactly right - I'd applied one of the custom formats
first. Remove the VALUE(...) wrapper. It produces text, but can be
coerced to values if needed in subsequent calculations.

Bill, since you have a very large number of cells to reformat (or
reformulate), a macro can do the job for you. Copy the code below in a
module, then when the sheet to reformat is the active one execute the macro.
The cells will still contain their original formula, so even if source data
is changed data will stay up-to-date.


Sub convert_percent()
d = Application.International(xlDecimalSeparator)
For Each c In ActiveSheet.UsedRange
If c.NumberFormat = "0.00%" And Application.IsNumber(c.Value) Then
f = Right(c.Formula, Len(c.Formula) - 1)
c.Formula = "=TEXT(" & f & ",LEFT(""0" & d & _
"00"",1 + 2*(MOD(ROUND(" & f & _
"*100,2),1)>0) + (MOD(ROUND(" & f & _
"*1000,1),1)>0)) & ""%"")"
c.NumberFormat = "General"
End If
Next
End Sub


JL
Mac OS X 10.3.7, Office v.X 10.1.6

 

[Bill Weylock]

Thanks so much.

That is exactly what I needed!

Only thing is that I am a stumblebum novice at using macros. I may have
screwed something up, or there may be a tiny error somewhere in the macro.

I created a VBA module in the workbook, pasted your code into it, and saved
it.

When I started to run it, things hummed along for a few seconds, but then I
got an error:

Run-time error Œ1004¹

Application-defined or object-defined error.

I clicked the Debug button and selected ³Step through².

The four lines I have just colored red below were in white against a red
background.

I read the help message, but it¹s over my head.

When I looked at the worksheet, most of the first table had been reformatted
perfectly. Looks as if it worked fine until it encountered 100.00%. Could
that be the problem? Wasn¹t looking for three digits to the left of the
decimal?

Thanks so much again!


Best,


- Bill

JE McGimpsey wrote on 2005/01/04 15:01:



Bill, since you have a very large number of cells to reformat (or
reformulate), a macro can do the job for you. Copy the code below in a
module, then when the sheet to reformat is the active one execute the macro.
The cells will still contain their original formula, so even if source data
is changed data will stay up-to-date.


Sub convert_percent()
d = Application.International(xlDecimalSeparator)
For Each c In ActiveSheet.UsedRange
If c.NumberFormat = "0.00%" And Application.IsNumber(c.Value) Then
f = Right(c.Formula, Len(c.Formula) - 1)
c.Formula = "=TEXT(" & f & ",LEFT(""0" & d & _
"00"",1 + 2*(MOD(ROUND(" & f & _
"*100,2),1)>0) + (MOD(ROUND(" & f & _
"*1000,1),1)>0)) & ""%"")"
c.NumberFormat = "General"
End If
Next
End Sub


JL
Mac OS X 10.3.7, Office v.X 10.1.6

Panther 10.3.6
Office 2004
Windows XP Pro SP2
Office 2003

 

[J Laroche]

The macro stopped on a cell in which 100.00% was directly typed instead of
being the result of a formula. You can bypass the problem with this new
version, which will ignore those cells without a formula. However if the
cell¹s formula is =1 (or any other value with the 0.00% format), the result
will be correct but left-aligned, so you can spot it easily.

Sub convert_percent()
d = Application.International(xlDecimalSeparator)
For Each c In ActiveSheet.UsedRange.Cells
If c.NumberFormat = "0.00%" And Application.IsNumber(c.Value) Then
f = Right(c.Formula, Len(c.Formula) - 1)
End If
End If
Next
End Sub



JL
Mac OS X 10.3.7, Office v.X 10.1.6


Bill Weylock wrote on 2005/01/04 22:48:

 

[Bill Weylock]

That is so great.

Hope I can do something for you sometime. Considering the gap in expertise
.... Need your sock drawer arranged?

Thanks so much!


Best,


- Bill

The macro stopped on a cell in which 100.00% was directly typed instead of
being the result of a formula. You can bypass the problem with this new
version, which will ignore those cells without a formula. However if the
cell¹s formula is =1 (or any other value with the 0.00% format), the result
will be correct but left-aligned, so you can spot it easily.

Sub convert_percent()
d = Application.International(xlDecimalSeparator)
For Each c In ActiveSheet.UsedRange.Cells
If c.NumberFormat = "0.00%" And Application.IsNumber(c.Value) Then
f = Right(c.Formula, Len(c.Formula) - 1)

End If
End If
Next
End Sub



JL
Mac OS X 10.3.7, Office v.X 10.1.6


Bill Weylock wrote on 2005/01/04 22:48:

Panther 10.3.6
Office 2004
Windows XP Pro SP2
Office 2003

 

[Bill Weylock]

Curious...

Is this part of your business, or have you learned this along the way toward
doing other things?


Best,


- Bill

The macro stopped on a cell in which 100.00% was directly typed instead of
being the result of a formula. You can bypass the problem with this new
version, which will ignore those cells without a formula. However if the
cell¹s formula is =1 (or any other value with the 0.00% format), the result
will be correct but left-aligned, so you can spot it easily.

Sub convert_percent()
d = Application.International(xlDecimalSeparator)
For Each c In ActiveSheet.UsedRange.Cells
If c.NumberFormat = "0.00%" And Application.IsNumber(c.Value) Then
f = Right(c.Formula, Len(c.Formula) - 1)

End If
End If
Next
End Sub



JL
Mac OS X 10.3.7, Office v.X 10.1.6


Bill Weylock wrote on 2005/01/04 22:48:

Panther 10.3.6
Office 2004
Windows XP Pro SP2
Office 2003

 

[Pf3at]

hi Bill

You commented on my Disk Repair error somewhere nearby. Good comment.

Regarding this % thing, I have some thoughts that have nothing to do
with Excel. I'm a tech writer who spends a lot of time massaging
information so that people can understand it without actually having to
think or hang up their cell phone. I'm wondering who will read this
data. I'm wondering who really reads 6000 lines of boring text. I'm
wondering who cares about a fraction of a percent on half of the items
on the list. I'd probably want to round those figures to the nearest
whole percent.

When I have this kind of data I look for a graphical way to represent
it so that readers can quickly get the big picture. Then, people who
really want to dig into the details can look at the raw data. For those
few people, I wouldn't make a big effort to clean it up too much.

If you do find a way to use mixed whole and fractional percent figures,
how will they be displayed? I'm sure Excel would want to right justify
them, making them virtually unreadable as you scan down a column. Left
justification might be better, but still visually distracting.

hope this helps
Tom

 

[Bill Weylock]

Thanks, Tom. I agree with you on all counts.

No one will read it, but people will scan it.

I did describe it back in the beginning as cross-tabs of a survey: the kind
of thing provided in an appendix, if at all.

This kind of thing is provided only on client request and is usually done by
a tab house in format that is much uglier than this.

In their software, however, they have a lot of formatting options tailored
to this precise kind of output. Excel doesn¹t. When they give me a nasty
looking version, I just ask them to clean it up and say how.

We did not do data collection on this study. Please do not ask why.

That is why we are in the odd position of delivering cross-tabs without
proper software.

In the end, you¹re absolutely right. I can deliver the tables exactly the
way they are without generating a lot of notice.

It¹s just that anything that leaves here should look better than this. I
don¹t like dumb looking tables, even when they are delivered to people who
don¹t care about them. I guess it could be called stubbornness, or (if
you¹re feeling generous) marketing. Also, there are some things that I would
like to check personally. Having a readable table makes that a lot easier.

I already chose center alignment for all of the numbers and headings. Works
great.

Thanks for taking the time. I do appreciate it.

Oh... We are definitely using graphics (as well as much simpler tables
pulled from these) in the actual presentation.

I¹ll check your disk thread now.


Best,


- Bill

hi Bill

You commented on my Disk Repair error somewhere nearby. Good comment.

Regarding this % thing, I have some thoughts that have nothing to do
with Excel. I'm a tech writer who spends a lot of time massaging
information so that people can understand it without actually having to
think or hang up their cell phone. I'm wondering who will read this
data. I'm wondering who really reads 6000 lines of boring text. I'm
wondering who cares about a fraction of a percent on half of the items
on the list. I'd probably want to round those figures to the nearest
whole percent.

When I have this kind of data I look for a graphical way to represent
it so that readers can quickly get the big picture. Then, people who
really want to dig into the details can look at the raw data. For those
few people, I wouldn't make a big effort to clean it up too much.

If you do find a way to use mixed whole and fractional percent figures,
how will they be displayed? I'm sure Excel would want to right justify
them, making them virtually unreadable as you scan down a column. Left
justification might be better, but still visually distracting.

hope this helps
Tom

Panther 10.3.6
Office 2004
Windows XP Pro SP2
Office 2003

 

[J Laroche]

After you reported the error caused by some cells I modified the macro but
doing it a 6AM was not a good idea, as I made some terrible mistakes. Now I
rethought the whole concept and I think this will be exactly what you
wanted. The numbers are better aligned in the second macro but the only
drawback is that it shows a decimal point even when there is no fraction.
Can you live with that? Or do you prefer the repaired first version?

Sub convert_percent()
d = Application.International(xlDecimalSeparator)
For Each c In ActiveSheet.UsedRange.Cells
If c.NumberFormat = "0.00%" And Application.IsNumber(c.Value) Then
f = c.Formula
a = c.HorizontalAlignment
If Left(f, 1) = "=" Then f = Right(f, Len(f) - 1)
c.Formula = "=TEXT(" & f & ",LEFT(""0" & d & _
"00"",1 + 2*(MOD(ROUND(" & f & _
"*100,2),1)>0) + (MOD(ROUND(" & f & _
"*1000,1),1)>0)) & ""%"")"
c.NumberFormat = "General"
c.HorizontalAlignment = a
End If
Next
End Sub


Sub convert_percent2()
For Each c In ActiveSheet.UsedRange.Cells
If c.NumberFormat = "0.00%" Then c.NumberFormat = "?.??%"
Next
End Sub


I need my basement sorted...

JL
Mac OS X 10.3.7, Office v.X 10.1.6

 

[Bill Weylock]

Jean -


Merci beaucoup!

I much prefer the first!

In fact, I just ran it. Gave me exactly what I had been asking for.

Not only that, but it works twice as fast! And my colors are bright, while
my whites are whiter than white!

Happy to help with sorting your basement. It should go just underneath the
first floor.

Anything else?


Best,


- Bill

P.S. This was exceptional help that someone obviously could charge for.
Thanks so much again!

After you reported the error caused by some cells I modified the macro but
doing it a 6AM was not a good idea, as I made some terrible mistakes. Now I
rethought the whole concept and I think this will be exactly what you
wanted. The numbers are better aligned in the second macro but the only
drawback is that it shows a decimal point even when there is no fraction.
Can you live with that? Or do you prefer the repaired first version?

Sub convert_percent()
d = Application.International(xlDecimalSeparator)
For Each c In ActiveSheet.UsedRange.Cells
If c.NumberFormat = "0.00%" And Application.IsNumber(c.Value) Then
f = c.Formula
a = c.HorizontalAlignment
If Left(f, 1) = "=" Then f = Right(f, Len(f) - 1)
c.Formula = "=TEXT(" & f & ",LEFT(""0" & d & _
"00"",1 + 2*(MOD(ROUND(" & f & _
"*100,2),1)>0) + (MOD(ROUND(" & f & _
"*1000,1),1)>0)) & ""%"")"
c.NumberFormat = "General"
c.HorizontalAlignment = a
End If
Next
End Sub


Sub convert_percent2()
For Each c In ActiveSheet.UsedRange.Cells
If c.NumberFormat = "0.00%" Then c.NumberFormat = "?.??%"
Next
End Sub


I need my basement sorted...

JL
Mac OS X 10.3.7, Office v.X 10.1.6

Panther 10.3.6
Office 2004
Windows XP Pro SP2
Office 2003