%s in XL entered as ".5" OR "50" reads as 50% if format is %age.

[Guest]

I have a spreadsheet I work with every day and I use fractional percentages,
between 0-1%. If the field is formatted as Percentage, and I type in ".5", it
reads it as 50%, which it would be if it weren't a percentage. But it is.
It's a half percent. By typing in "50", I also get 50%, which doesn't make
sense. If I type in "5" I get 5%, if I type in ".5", I should get .5%. Is
there a way to make this happen, or is this just a glitch in Excel? Please
help me figure this out. Thanks. (e-mail address removed)

 

[Anne Troy]

Try this, Buzz:
http://www.officearticles.com/excel/data_is_entered_at_10_percent_its_value_in_microsoft_excel.htm
*******************
~Anne Troy

www.OfficeArticles.com

 

[Guest]

You might want to try to enter it as .05, leave formatted as a percentage
with 2 decimal points. I've come across this in other programs also.

 

[Ragdyer]

What version are you using?

Right now I'm on a XL97 machine.
Formatting a range to % with 2 decimal places, and entering:
..5
and
0.5
Give 2 completely different results!
..5 = 50.00%
0.5 = 0.50%

Now, I remember seeing on my XL02 machine, a setting in,
<Tools> <Options> <Edit> tab,
A setting that I don't have on my 97 machine.
Something relating to %'s ... something about % entries (can't quite
remember without looking).

Anyway, if your version has this option in the <Edit> tab, try making
various entries with it checked and unchecked, and see it helps.
(Can't check mine 'til tomorrow morning)

 

[Guest]

Hi

Using Windows 2000 and Office XP I have the same situation at times. Plesae
note the at times part.

Since I never work with fractions, this has never bothered me. Strange
enough, if you, with some percentages in your sheet, untick the Allow
automatic % entry option, and you type in 50, you will get 5000%, 5 will show
500%, .5 will show 50%, .05 will show 5% and so on
Do not untick that option, for your own sanity's sake

 

[Guest]

That didn't help, unfortunately. That box was already unchecked for me. Try
formatting a cell as Percentage, then type in 5 and .05 and you get the same
result. You should get 5% and 0.05% respectively. Doesn't make sense. If you
have any other suggestions, I'd be grateful.

 

[Guest]

If I enter it as .05, it reads 5%. If I wanted 5%, I'd type in 5, right? It's
actually formatted with 3 decimal points. Try it on your Excel, you'll see,
typing in .05 and 5 both gives 5%, when it should be 0.05% and 5%,
respectively. I'm thoroughly confused. If you have any other suggestions, I'd
appreciate it.

 

[Guest]

I don't really understand. I typed in ..5 and got "..5". No percentages. Why
2 decimal points? If you try on your excel, you'll see that .05 and 5
entered, both give you "5%", when they should give you 0.05% and 5.00%,
respectively. I still don't get it. If you check on your other computer and
come up with something, please let me know. Thanks. I'm using Excel 2002.

 

[Guest]

I don't understand. Where is the check box you're referring to? And I
couldn't really tell from your response if this was a solution to the
problem, or just another problem that you've faced that's loosely related.
Could you clear that up for me? And if you have any help I would really
appreciate it. Thanks.

 

[RagDyer]

I *DIDN'T* say 2 decimal *POINTS*, I said 2 decimal *PLACES*.

Anyway, re-read my post.
You'll see where I described entering
..5 = 50.00%
And entering
0.5 = 0.50%

So, the answer to your question is to enter a zero before the decimal point,
and then you'll get exactly what you're looking for.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Guest]

Hi

I merely confirmed that this is a problem in Excel. If you want to enter
5%, there is really only one way, and that is to enter it as .05. If you
want .5%, enter it as .005. Stupid, I know.
The button I referred to, you will find under Tools|Options. Select the
Edit tab, and you have the option to automatically enter numbers as %. If
you untick this option, it goes completely haywire. So I only tried to warn
you on the one hand, and to confirm that we do have a problem. The solution
- who knows?

 

[Guest]

I've had the same problem, and it is annoying. It's counterintuitive that
these two different keystroke sequences (".5" and "50") result in the *same*
number when the cell is formatted as a percentage. The solution is to type
the leading zero. For instance, enter "0.5" instead of ".5" to get the
correct display of 0.5%.

 

[Niek Otten]

Tools>Options>Edit tab>Enable automatic percent entry> governs the behavior of Excel for percentages.
Confusing, I agree.
But then again, the previous behavior was confusing to many too.....

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| I've had the same problem, and it is annoying. It's counterintuitive that
| these two different keystroke sequences (".5" and "50") result in the *same*
| number when the cell is formatted as a percentage. The solution is to type
| the leading zero. For instance, enter "0.5" instead of ".5" to get the
| correct display of 0.5%.
|
| "Abuzzmaster" wrote:
|
| > I have a spreadsheet I work with every day and I use fractional percentages,
| > between 0-1%. If the field is formatted as Percentage, and I type in ".5", it
| > reads it as 50%, which it would be if it weren't a percentage. But it is.
| > It's a half percent. By typing in "50", I also get 50%, which doesn't make
| > sense. If I type in "5" I get 5%, if I type in ".5", I should get .5%. Is
| > there a way to make this happen, or is this just a glitch in Excel? Please
| > help me figure this out. Thanks. (e-mail address removed)