MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Data validation for cents


Posted by Jim on January 31, 2002 11:48 AM

How can I use data validation or something similar to reject an attempted cell entry if it is not entered with two decimal places? The number is monetary but must include the cents, even if they are .00 .


Posted by Steve Hartman on January 31, 2002 12:18 PM

Why not just format the cell for currency?

Why not just format the cell for currency?

Posted by Jim on January 31, 2002 12:33 PM

Re: Why not just format the cell for currency?

I don't think that would ensure that each entry included cents. If someone entered a whole number, it would appear as xxx.00 . If the actual amount ends in .00 that's OK but I'm trying to foolproof the sheet so I know they entered xxx.xx .

Posted by Steve Hartman on January 31, 2002 1:48 PM

=LEFT(RIGHT(TEXT(A1,"general"),3),1)="."


Assuming your imput cell is A1:

In Data Validation for cell A1 choose Custom and put in this formula. =LEFT(RIGHT(TEXT(P48,"general"),3),1)="."

It will only allow numbers with 2 decimals to be entered. The Excel help file and my excel books say you can't use general as an argument for the TEXT() function but I tried it anyway and it works. It is the only way to get the text representation of a number without having to specify # of decimal places, which would make this task impossible in a function.

Posted by Steve Hartman on January 31, 2002 1:56 PM

Re: =LEFT(RIGHT(TEXT(A1,"general"),3),1)="."

Obviously the formula SHOULD be:

=LEFT(RIGHT(TEXT(A1,"general"),3),1)="."

Posted by Jim on January 31, 2002 3:22 PM

Works great except when xxx.00

It will only allow numbers with 2 decimals to be entered. The Excel help file and my excel books say you can't use general as an argument for the TEXT() function but I tried it anyway and it works. It is the only way to get the text representation of a number without having to specify # of decimal places, which would make this task impossible in a function.

This formula works great except when the entry legitimately ends in .00. The TEXT function doesn't pick up the .00 . I thought about making it an OR function but haven't figured out how to still make it exclude whole number entries without cents.

Posted by Aladin Akyurek on January 31, 2002 3:46 PM

How about

formatting the cell as text (right-aligned for visual reasons) and using the following formula in Data Validation:

=AND(ISNUMBER(P48+0),ISNUMBER(SEARCH(".",P48)),LEN(P48)-SEARCH(".",P48)=2)

This requires that when you want to compute with the value of P48, you need to use

P48+0

in your formulas.

===========

Posted by Steve Hartman on January 31, 2002 6:55 PM

Re: Works great except when xxx.00

Format your input cell as number or currency with two decimal places and it will work fine. At least it does on my Excel 97. It also leaves the number aas a number so you don't have to add 0 to use it as a number again.

Posted by Jim on February 01, 2002 5:18 AM

Re: Works great except when xxx.00


But one of the requirements is the input cell must display rounded to the nearest dollar.

Posted by Jim on February 01, 2002 5:50 AM

Validation works but I need to display whole number only

This works perfectly for data validation but (obviously) causes the cell to display exactly what's entered. It needs to display the entry rounded to the nearest whole number, without any decimal places showing. formatting the cell as text (right-aligned for visual reasons) and using the following formula in Data Validation: =AND(ISNUMBER(P48+0),ISNUMBER(SEARCH(".",P48)),LEN(P48)-SEARCH(".",P48)=2) This requires that when you want to compute with the value of P48, you need to use P48+0 in your formulas. ===========

Posted by Aladin Akyurek on February 01, 2002 1:53 PM

Re: Validation works but I need to display whole number only

Jim --

You can't validate a cell for a 2 decimal input and at the same round the validated input into a whole number in the same cell.

Care to elaborate?

Aladin

======== This works perfectly for data validation but (obviously) causes the cell to display exactly what's entered. It needs to display the entry rounded to the nearest whole number, without any decimal places showing. : formatting the cell as text (right-aligned for visual reasons) and using the following formula in Data Validation

Posted by Steve Hartman on February 03, 2002 12:06 PM

ARRRGGHHHH!

But you never said this before!