Data Validation for Prices in Cells

[Ryan H]

Out of all the years I've used Excel I've never used data validation before
and need help. I have a list of part numbers (Col.A), part descriptions
(Col.B), and part prices (Col.C). I want to ensure the user enters a valid
price in Col. C. It can be any price, but must be numeric. I have Col.C
formatted as Accountanting. And ideas?

Thanks in Advance!

 

[EricG]

Data/Validation --> Select Decimal? That will ensure the user enters a
decimal number. You can use the min/max ranges to make sure the number is
reasonable. The only thing this won't do is ensure that the decimal number
as only two places, so the user could enter 45.32384.

If you want to enforce the two decimal place requirement, you would probably
have to use the "Custom" selection and create an appropriate formula.

HTH,

Eric

 

[ker_01]

With the custom formula option, you can do something like:

=AND(A1 = round(A1,2), A1>=3.50, A1<4.50)
this forces the number to be 2 decimal places, and at least 3.5 and no more
than 4.5

note: This doesn't work well for percentages (round(A1,4) for a percent with
2 decimals) because if the entry fails against the data validation rule, when
it highlights the cell for re-entry, it does not treat the newly entered
number as a percent, so you are pretty much guaranteed to be outside your
min/max value ranges.

HTH,
Keith