Data Validation error (XL2003 and XL2007)

  • Thread starter Thread starter ker_01
  • Start date Start date
K

ker_01

Hopefully someone from MS monitors this board and processes posts for review
and fixes in future versions, I'd hate to think I'm wasting my time posting
errors/problems.

Excel2003, also confirmed same result in 2007

Select a cell

Set cell format to percentages (e.g. 2.2%)

Apply custom data validation with the following formula:
=A1=ROUND(A1,3)
this forces the percent to be no more than one decimal place (2.2% is ok,
2.22% is not)

Mouse into the cell and change the cell value to another acceptable value by
entering 2.1 (do not enter .021 or 2.1 followed by the % sign- just enter 2.1
to confirm that the cell then shows 2.1%)

Enter a value with 2 or more decimal places to trigger the data validation
warning, e.g. 2.22

When presented with the options of "Retry" or "Cancel", select 'Retry'

Enter the corrected value of 2.2

Expected behavior: cell should show 2.2%, just like it did when you
hand-entered a valid value into that cell
Actual behavior: cell shows 220%

The value being entered is seen as a regular number, not as a percent, then
is being formatted by the cell into the percentage which is 100x the expected
number

Some might argue that this is expected behavior- to enter a percentage I
should enter [.022]. I'd argue that this is a consistency issue/error: in
comparison, if you cursor into the cell manually and overtype 2.1 (or any
number to one decimal), it is recognized as a change to the appropriate
percentage, and does not revert to an integer number formatted as a
percentage- even though the user does not type the extended decimal value
[.022] nor is required to add a percentage sign for each edit [2.2%]. There
is no functional difference from the user perspective between cursoring into
that cell to change it, and having the data validation highlight the cell and
allow you to change it. The same user behavior should result in the same
output under the same user conditions.

Thank you,
Keith
 
I should add- the reason this is critical in my case is because the data
validation actually has three conditions- one decimal place, and the overall
percentage has to be within a specified range, e.g.
=AND(A1=ROUND(A1,3),(A1>B1),(A1<C1))

the ranges for entry are rather small, such as 3.2% to 4.3%

So if a user tries to enter too many decimal places, they get an error; they
try to correct their error by hitting "retry" and entering to only one
decimal place, and they still get an error but they won't see the larger
value (it doesn't populate the cell with the incorrect value before throwing
the error). They won't know that they are getting an out of range error (with
only one data validation per cell, the data validation message can't be that
specific). The best I could hope for is that they are really smart and figure
it out, or that they eventually hit cancel and start from scratch again
(which is terrible UI design).

ker_01 said:
Hopefully someone from MS monitors this board and processes posts for review
and fixes in future versions, I'd hate to think I'm wasting my time posting
errors/problems.

Excel2003, also confirmed same result in 2007

Select a cell

Set cell format to percentages (e.g. 2.2%)

Apply custom data validation with the following formula:
=A1=ROUND(A1,3)
this forces the percent to be no more than one decimal place (2.2% is ok,
2.22% is not)

Mouse into the cell and change the cell value to another acceptable value by
entering 2.1 (do not enter .021 or 2.1 followed by the % sign- just enter 2.1
to confirm that the cell then shows 2.1%)

Enter a value with 2 or more decimal places to trigger the data validation
warning, e.g. 2.22

When presented with the options of "Retry" or "Cancel", select 'Retry'

Enter the corrected value of 2.2

Expected behavior: cell should show 2.2%, just like it did when you
hand-entered a valid value into that cell
Actual behavior: cell shows 220%

The value being entered is seen as a regular number, not as a percent, then
is being formatted by the cell into the percentage which is 100x the expected
number

Some might argue that this is expected behavior- to enter a percentage I
should enter [.022]. I'd argue that this is a consistency issue/error: in
comparison, if you cursor into the cell manually and overtype 2.1 (or any
number to one decimal), it is recognized as a change to the appropriate
percentage, and does not revert to an integer number formatted as a
percentage- even though the user does not type the extended decimal value
[.022] nor is required to add a percentage sign for each edit [2.2%]. There
is no functional difference from the user perspective between cursoring into
that cell to change it, and having the data validation highlight the cell and
allow you to change it. The same user behavior should result in the same
output under the same user conditions.

Thank you,
Keith
Click to expand...
 
Back
Top