Percent Format Not working Properly for Percents Less Than 1

[LarryFromVegas]

I have an input cell formatted as Number/Percentage/4. This cell needs to display the user's input without the user having to type the percent sign. It is used to express some small tax percentages, oftentimes less than 1%. This cell is used in other places on the sheet to perform calculations, and so precision and consistency of input are extremely important. My issue is with the inconsistent display of the input values.

When the user types "1" (meaning 1%, the same as the decimal .01), the cell correctly displays as 1.0000 %.

When the user types "10" (meaning 10%, the same as the decimal .10), the cell correctly displays 10.0000 %.

When the user types "100" (meaning 100%, the same as the decimal 1.00), the cell correctly displays 100.0000 %.

HOWEVER when the user types .1 (meaning .1% --- aka 1/10% --- the same as the decimal .001), the cell INCORRECTLY displays as 10%. It should display as .1%; treating it similarly to the others shown above. Excel responds as if the user made an error and doesn't want to express a decimal of a percentage, but that is an incorrect assumption.

It appears that Excel will only treat the entry as a percentage as long as that it is greater than or equal to 1. But when the entry is less than one, it multiplies that amount by 100.

For a temp fix, I WAS able to get Excel to correctly put the percent sign in if the user were to type ".1%" [needed to type the percent sign after the number]. But the problem with that is it is not intended that the user have to type the percent sign when entering text into that cell. In this application, it would be very confusing and inconsistent for the user to type the percent sign in some instances (if less than 1) and not type the percent sign when greater than 1%.

I have searched the Internet and cannot find a way to change this so far. Would settle for a custom format to get Excel to properly express this cell entry as a percent as long as that didn't mess up any related calculations.

 

[vlady]

how about

0.00%;(-0.00%)

 

[jproffer]

If they type 0.1 instead of just .1 it will display properly...no "%" sign needed.

 

[repairman615]

Typing zero point one (0.01)
seems to work
or zero point zero one (0.01)for tenths


A leading zero before the decimal maybe signals a fraction of a percent?

It doesn't seem very intuitive.

 

[RonB1111]

In Excel Options, you need to uncheck "Enable automatic percent entry".

In 2007+ click the Office Button in the upper left corner, click Excel Options at the bottom, click Advanced and you'll see it in the right hand pane in the 1st section.

In prior versions, click Tools-Options and it's on the Edit tab and on the right side of the pane.

 

[LarryFromVegas]

Thanks for all the replies.

Vlady: negative numbers were the cuase of the problem; it was caused by positive decimal percents.

jproffer, repairman615: gr8 solution. I think that'll do it. Am building a pop-up tool tip for all the input cells on that sheet, soI can remind the users about typing the leading zero for fractional percents.

RonB1111: I have to e-mail this sheet to other users. Correct me if I'm wrong, but I don't think your suggested setting resides within the spreadsheet. So without a lot of fancy progrmming of their Excel interface upon auto_open I won't be able to control that setting once this gets to the hands of the users.

I'm happy; you're smart.

 

[vlady]

hi again

don't now if this is applicable.
they enter in A1.... will be data entry

in B1

=IF(INT(A1)-INT(A1)=0,M2/100,A1)
**checks if the number entered is decimal..

So B1 will be the true column for % to be used for computations

if this can be in vba better.

 

[repairman615]

hi again

don't now if this is applicable.
they enter in A1.... will be data entry

in B1

=IF(INT(A1)-INT(A1)=0,M2/100,A1)
**checks if the number entered is decimal..

So B1 will be the true column for % to be used for computations

if this can be in vba better.

I did attempt a change event to test if the cell changed was less than 1 and if that was true, then I multiplied by .1 which .1 entered would be 10% and the 10% * 0.01 = %0.1


However being as 100 percent is 1, it was too buggy for me to continue

Basically I found that entering 10 into the cell while formatted as a percent = .1 upon the change, making a legitiment 10 also %0.1

At one point it would kindof work if the cell was 'reset' to 1 or 100%.