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.

hmaze

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

FormatecellpercentageOK

Mike Rogers

"hmaze" wrote:

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 - followed your instructions, however, when I type 1.23 in after
pre-formatting the cell, it shows as 123.00%

"Mike Rogers" wrote:

hmaze

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

FormatecellpercentageOK

Mike Rogers

"hmaze" wrote:

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.

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" wrote in message
...
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.

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" wrote:

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" wrote in message
...
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.

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" wrote in message
...
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" wrote:

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" wrote in message
...
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.

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

"Ron Coderre" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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.

Tools/ Options/ Edit:
Enable automatic percent entry.

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

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

"Ron Coderre" wrote:

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" wrote in message
...
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" wrote:

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" wrote in message
...
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.