Formatting numbers to percentage

excel  2010
I have cells that no matter what i do to format as percentage  2 decimals..

Example:
Originally the cells were "General"  37.44
I format as "Percentage" to 2 decimals

They show up as  3700.00%  Instead of 37.00%
I checked the formatting of the cells  and it clearly shows me  percentage  and 2 decimals ?

Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
2018-02-21_08-12-51.pngThat's by design.
0
FordraidersAuthor Commented:
Martin,  ok, But surely, there has to be a way to change the value to a percentage ?
0
Fabrice LambertFabrice LambertCommented:
Hello,

enter a custom format: 0.00%
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Martin LissOlder than dirtCommented:
Here's a macro you can use. Select the cells you want to change and then run the macro.

Sub MakePercent()
Selection = Selection / 100
Selection.NumberFormat = "0.00%"
End Sub

Open in new window

0
Fabrice LambertFabrice LambertCommented:
Ops, I spoke too fast, add double quotes before and after the %:
0.00"%"
0
Rob HensonFinance AnalystCommented:
You can convert the numbers to a decimal by dividing by 100.

Type 100 into an empty cell and press Ctrl C to copy it to the clipboard.

Select the cells containing the values that require change and go to the Paste Special dialogue. In the top section select Values and in the middle section select Divide, click OK. All the numbers in the selected cells will now be replaced with the number divided by 100; you can now format as percentage as expected.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
Rob, Thanks works great..

Now a another issue:  
I need only select cells in Column G(LESS THE HEADER)
That do notchoosing cells not already formatted as percentage have the percentage sign, or are already formatted as  Percentage ?

Then I would need to perform your steps ?

This can also be another question if need be..

Thanks
fordraiders
0
Rob HensonFinance AnalystCommented:
Apply a filter to column G and filter to only show values greater than 1 (assuming all entries represent a value less than 100%).

With the filter in place and selecting all the visible cells, the Paste Special as described above should only affect the visible cells.

Alternatively, in a spare column try the following formula:

=IF(G2<1,G2,G2/100)

Copy down as required and then Copy the column and paste as values into column G; again assumes all entries represent a value less than 100%.
0
Rob HensonFinance AnalystCommented:
If there is a risk that the values in G are actually text representations of percentage values, then:

=IF(VALUE(G2)<1,VALUE(G2),VALUE(G2)/100)
0
FordraidersAuthor Commented:
Rob, Thank You, just because i know someone will use 110%  what will happen ?

Thanks
fordraiders
0
FordraidersAuthor Commented:
Rob,
ok, I see the ramification of over 100% ...is there a way around that issue ?
0
FordraidersAuthor Commented:
Thanks all !
0
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
Rob HensonFinance AnalystCommented:
I think there is a way of checking the number other than comparing to 1 using MOD and RIGHT, try this formula:

=IF(ISTEXT(G2),VALUE(G2),IF(TEXT(MOD(G2,1),"0.00")=TEXT(RIGHT(G2,2)/100,"0.00"),G2/100,G2))

If G2 is text, it assumes it is already in 0.00% format and evaluating with a VALUE statement will convert it correctly as Excel will recognise the % symbol in the text string.

Other scenarios:
1) a number, eg 37.44 which should be 37.44%, ie needs dividing by 100
2) already a percentage eg 37.44% so can be left as is

A major assumption that all numbers will only be 2 decimals!!

With the following two formulae, you get different results depending on the scenario:

=MOD(G2,1)  in scenario 1 with a value of 37.44 gives result of 0.44
=RIGHT(G2,2)/100  against a value of 37.44 also gives 0.44

=MOD(G2,1)  in scenario 2 with a value of 37.44% gives result of 0.3744
=RIGHT(G2,2)/100  against a value of 37.44% still gives 0.44

Therefore by comparing those two formulae for each number you can determine whether it is a number or a percentage. Enclosing each in TEXT(Value,"0.00") just rounds the results back to 2 decimals for ease of comparison.

In the scenario where value is potentially over 100%, eg 137.44 or 137.44%; the 137.44 still gives 0.44 in both and the 137.44% gives 0.37 and 0.44 as before.

See attached with a few samples.
Percentage-check.xlsx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.