Format Percentage

H

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

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
 
R

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

hmaze

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

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

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

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

David Biddulph

Tools/ Options/ Edit:
Enable automatic percent entry.

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

hmaze

That did it - thank you all.

David Biddulph said:
Tools/ Options/ Edit:
Enable automatic percent entry.

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

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

David Biddulph

Glad to have been able to help.
--
David Biddulph

hmaze said:
That did it - thank you all.

David Biddulph said:
Tools/ Options/ Edit:
Enable automatic percent entry.

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

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

:

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)

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)

:

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)



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.
Click to expand...
Click to expand...
Click to expand...
 
Top