Format Percentage

[hmaze]

All:

I need the user to type in the percentage as: one dot two three % (1.23%)
and the cell should show 1.23% but for calculations it should use 0.0123 -
can I do this in custom formatting? Thank you.

 

[Mike Rogers]

hmaze

Formate the cell as a percentage BEFORE you have the data entered and it
will do what you want.

Formate>cell>percentage>OK

Mike Rogers

 

[Ron Coderre]

If you format the cells as Percent:

<format><cells><number tab>
Category: Percentage

....or...
Ctrl+Shift+%....(the keyboard shortcut)

then, what you're describing is Excel's default behavior.

Example:
Select A1
Ctrl+Shift+%
Enter 1.23..Press Enter......cell displays as 1.23%
B1: =A1*1......displays 0.0123

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[hmaze]

Mike - followed your instructions, however, when I type 1.23 in after
pre-formatting the cell, it shows as 123.00%

 

[hmaze]

Ron, I followed your and Mike's instructions however, the cell will read
123.00% - is there an option I have to change somewhere (using Excel 2003)

 

[Ron Coderre]

It sounds like you have "fixed decimal places" engaged

Try this:
From the Excel Main Menu:
<tools><options><edit tab>...UNcheck: Fixed Decimal Places.

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[hmaze]

'Fixed Decimal Places' was already unchecked, I made sure and un/checked it a
few times. Any other suggestions? Appreciate the help and time.

 

[David Biddulph]

Tools/ Options/ Edit:
Enable automatic percent entry.

[Otherwise, try entering 1.23% instead of just 1.23]

 

[hmaze]

That did it - thank you all.

Tools/ Options/ Edit:
Enable automatic percent entry.

[Otherwise, try entering 1.23% instead of just 1.23]
--
David Biddulph

'Fixed Decimal Places' was already unchecked, I made sure and un/checked
it a
few times. Any other suggestions? Appreciate the help and time.