Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Limiting Validation entries to 2 places after a decimal
I have a multi-user spreadsheet to enter dollar values. How can I limit a
user from entering, for example, 43.703 when the correct entry is 43.70? I can do this by changing the number format but do not want to do that. |
#2
|
|||
|
|||
Limiting Validation entries to 2 places after a decimal
You can do this with Data Validation on the Data menu. Open the
Data Validation box, choose Custom from the list, and use the formula =FIND(".",A1,1)=LEN(A1)-2 Change A1 to the appropriate cell reference. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Tim" wrote in message ... I have a multi-user spreadsheet to enter dollar values. How can I limit a user from entering, for example, 43.703 when the correct entry is 43.70? I can do this by changing the number format but do not want to do that. |
#3
|
|||
|
|||
Limiting Validation entries to 2 places after a decimal
I don't know how to do exactly what you are asking, but this might help.
Goto Tools-Options-Calculation and select "Precision as Displayed". Then format the fields to a number with two decimal places. Then when someone enters 43.703 it will convert the number to 43.70 automatically (not just the output). However if someone enters 43.709 it will convert the number to 43.71 automatically. This actually rounds the cell value to the nearest 100th place. It's not just changing the output, and you will permanently lose any precision beyond the 100th place. "Tim" wrote: I have a multi-user spreadsheet to enter dollar values. How can I limit a user from entering, for example, 43.703 when the correct entry is 43.70? I can do this by changing the number format but do not want to do that. |
#4
|
|||
|
|||
Limiting Validation entries to 2 places after a decimal
Select the cell (assume it's A1) do datavalidation and allow custom and use
this formula =MOD(100*A1,1)=0 -- Regards, Peo Sjoblom "Tim" wrote in message ... I have a multi-user spreadsheet to enter dollar values. How can I limit a user from entering, for example, 43.703 when the correct entry is 43.70? I can do this by changing the number format but do not want to do that. |
#5
|
|||
|
|||
Limiting Validation entries to 2 places after a decimal
One way:
Assume the validation is to be applied to cell A1: Allow: Custom Formula: =ABS(A1-ROUND(A1,2))<1E-8 In article , Tim wrote: I have a multi-user spreadsheet to enter dollar values. How can I limit a user from entering, for example, 43.703 when the correct entry is 43.70? I can do this by changing the number format but do not want to do that. |
#6
|
|||
|
|||
Limiting Validation entries to 2 places after a decimal
Note that this formula requires that the user enter the decimal point
(e.g., "2." rather than "2" for $2.00). In article , "Chip Pearson" wrote: You can do this with Data Validation on the Data menu. Open the Data Validation box, choose Custom from the list, and use the formula =FIND(".",A1,1)=LEN(A1)-2 Change A1 to the appropriate cell reference. |
#7
|
|||
|
|||
Limiting Validation entries to 2 places after a decimal
Good job. I assume the other suggestions work, but yours is the most
straight-foward. It should be noted however that if you select a range of cells to validate, "A1" should be replaced by top left cell in the selected region. Also, this might frustrate users who are not experienced with excel. The pop up does not explain the restriction, only the fact that it is restricted. But then again, my solution my frustate them as well, and would have worse consequences if they messed something up. "Peo Sjoblom" wrote: Select the cell (assume it's A1) do datavalidation and allow custom and use this formula =MOD(100*A1,1)=0 -- Regards, Peo Sjoblom "Tim" wrote in message ... I have a multi-user spreadsheet to enter dollar values. How can I limit a user from entering, for example, 43.703 when the correct entry is 43.70? I can do this by changing the number format but do not want to do that. |
#8
|
|||
|
|||
Limiting Validation entries to 2 places after a decimal
When you apply the validation you can select the error alert and type in
something like "You cannot enter more than 2 decimals!" -- Regards, Peo Sjoblom "Sloth" wrote in message ... Good job. I assume the other suggestions work, but yours is the most straight-foward. It should be noted however that if you select a range of cells to validate, "A1" should be replaced by top left cell in the selected region. Also, this might frustrate users who are not experienced with excel. The pop up does not explain the restriction, only the fact that it is restricted. But then again, my solution my frustate them as well, and would have worse consequences if they messed something up. "Peo Sjoblom" wrote: Select the cell (assume it's A1) do datavalidation and allow custom and use this formula =MOD(100*A1,1)=0 -- Regards, Peo Sjoblom "Tim" wrote in message ... I have a multi-user spreadsheet to enter dollar values. How can I limit a user from entering, for example, 43.703 when the correct entry is 43.70? I can do this by changing the number format but do not want to do that. |
#9
|
|||
|
|||
Limiting Validation entries to 2 places after a decimal
Oh, thank you. I hope you didn't take offence to my criticism. I don't use
data validation that often, obviously. I am just trying to learn as much as possible. "Peo Sjoblom" wrote: When you apply the validation you can select the error alert and type in something like "You cannot enter more than 2 decimals!" -- Regards, Peo Sjoblom "Sloth" wrote in message ... Good job. I assume the other suggestions work, but yours is the most straight-foward. It should be noted however that if you select a range of cells to validate, "A1" should be replaced by top left cell in the selected region. Also, this might frustrate users who are not experienced with excel. The pop up does not explain the restriction, only the fact that it is restricted. But then again, my solution my frustate them as well, and would have worse consequences if they messed something up. "Peo Sjoblom" wrote: Select the cell (assume it's A1) do datavalidation and allow custom and use this formula =MOD(100*A1,1)=0 -- Regards, Peo Sjoblom "Tim" wrote in message ... I have a multi-user spreadsheet to enter dollar values. How can I limit a user from entering, for example, 43.703 when the correct entry is 43.70? I can do this by changing the number format but do not want to do that. |
#10
|
|||
|
|||
Limiting Validation entries to 2 places after a decimal
No offense taken at all
-- Regards, Peo Sjoblom (No private emails please) "Sloth" wrote in message ... Oh, thank you. I hope you didn't take offence to my criticism. I don't use data validation that often, obviously. I am just trying to learn as much as possible. "Peo Sjoblom" wrote: When you apply the validation you can select the error alert and type in something like "You cannot enter more than 2 decimals!" -- Regards, Peo Sjoblom "Sloth" wrote in message ... Good job. I assume the other suggestions work, but yours is the most straight-foward. It should be noted however that if you select a range of cells to validate, "A1" should be replaced by top left cell in the selected region. Also, this might frustrate users who are not experienced with excel. The pop up does not explain the restriction, only the fact that it is restricted. But then again, my solution my frustate them as well, and would have worse consequences if they messed something up. "Peo Sjoblom" wrote: Select the cell (assume it's A1) do datavalidation and allow custom and use this formula =MOD(100*A1,1)=0 -- Regards, Peo Sjoblom "Tim" wrote in message ... I have a multi-user spreadsheet to enter dollar values. How can I limit a user from entering, for example, 43.703 when the correct entry is 43.70? I can do this by changing the number format but do not want to do that. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel adds phantom decimal places: why? | Excel Discussion (Misc queries) | |||
Max decimal places | Excel Discussion (Misc queries) | |||
FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. | Excel Discussion (Misc queries) | |||
decimal places within a formula | Excel Discussion (Misc queries) | |||
Changing default decimal places | Excel Discussion (Misc queries) |