Data validation for cents

Why not just format the cell for currency?

Why not just format the cell for currency?

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 .

=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.

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

Obviously the formula SHOULD be:

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

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.

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.

===========

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.

Re: Works great except when xxx.00

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

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. ===========

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

ARRRGGHHHH!

But you never said this before!