Custom Validation Formula

[Mbourque94]

I need to create a custom formula for a validation test with the requirement being 4 digits and a value within the range of 1000 and 6900. This could be simply accomplished by using whole number range and specifying the values, as it would be impossible to have an entry that was not 4 digits using whole numbers only. The requirements however specifically require a custom formula, which I have been trying and failing with for the past hour and a half. PLEASE HELP!!

 

[FDibbins]

Hi welcome to the board

TRy this...
=AND(A1>=1000,A1<=6900,LEN(A1)=4)

The requirements however specifically require a custom formula

and what requirements would they be?

 

[Aladin Akyurek]

Something like...

=AND(A1>=1000,A1<=6900,A1=TRUNC(A1))

 

[FDibbins]

Aladin, any decimal value with 4 digits will be less than 1000 and any decimal > 1000 with a decimal will be > 6900, that's why I just used LEN()

 

[Mbourque94]

Thanks for the quick response. All that is stated for specific instructions is " Create a custom data validation in cells J19:J23 that accept entry only if four digits in length and in a range from 1000 to 6999."

 

[FDibbins]

So, this is homework?

 

[Mbourque94]

No it isn't homework. I start a new job next week so I have been working on excel assignment from an accounting course I took last year to be better prepared for the amount I will rely on excel for the job.

 

[FDibbins]

Did you try either of the suggestion?

 

[Mbourque94]

I just tried both of them. They allow me to enter "999" which should prompt an error message if the rule was operating. Since I am using the cell range of J19:J23 I tried modifying the formula to =AND(J19:J23>=1000,J19:J23<=6900,LEN(J19:J23)=4 but I must have made a mistake because it will not accept the formula.

 

[FDibbins]

With a test for LEN()=4, you will not be able to enter 999, perhaps you applied it incorrectly?