Validate to quarter units
 

Is there a way to have the cells validate to quarter units? Specifically, I
only want users to be able to enter .00; .25; .50; or .75 numbers.
--
Thanks
Shawn

Validate to quarter units
 

Hi Shawn,

Not sure of the interpretation of your question. Do you mean that all values
must be less than one be in quarter increments or that the values can include
greater than one but must be in quarter increments.

Use Data validation. Select the range to be validated and enter the formula
as if it applies to the first cell of the selected range. (Excel looks after
applying it correctly to the remaining cells in the selection.)

If the first option above then the following in Data Validation formula.
=AND(MOD(A1,0.25)=0,A1<1)

If the second option then the following Data Validation formula.
=MOD(A1,0.25)=0

--
Regards,

OssieMac


"Shawn" wrote:

Is there a way to have the cells validate to quarter units? Specifically, I
only want users to be able to enter .00; .25; .50; or .75 numbers.
--
Thanks
Shawn

Validate to quarter units
 

On Thu, 25 Feb 2010 10:49:01 -0800, Shawn
wrote:

Is there a way to have the cells validate to quarter units? Specifically, I
only want users to be able to enter .00; .25; .50; or .75 numbers.

Data/Validation/Settings

Custom
Formula: =MOD(A1,0.25)=0

--ron

Validate to quarter units
 

The cells can be less than or greater than 1, but would never be greater than
24 or less than 0. This is on a time sheet and we caputre time to the
quarter unit. So a formula that would validate greater than 0, less than
24.00001 and to the quarter unit would be idea.
--
Thanks
Shawn


"OssieMac" wrote:

Hi Shawn,

Not sure of the interpretation of your question. Do you mean that all values
must be less than one be in quarter increments or that the values can include
greater than one but must be in quarter increments.

Use Data validation. Select the range to be validated and enter the formula
as if it applies to the first cell of the selected range. (Excel looks after
applying it correctly to the remaining cells in the selection.)

If the first option above then the following in Data Validation formula.
=AND(MOD(A1,0.25)=0,A1<1)

If the second option then the following Data Validation formula.
=MOD(A1,0.25)=0

--
Regards,

OssieMac


"Shawn" wrote:

Is there a way to have the cells validate to quarter units? Specifically, I
only want users to be able to enter .00; .25; .50; or .75 numbers.
--
Thanks
Shawn

Validate to quarter units
 

I got the following to do just what I wanted (kicks out less than 0, more
than 24, and anything that isn't on a quarter unit).

=IF(MOD(L1,0.25)<0,FALSE,IF(L1<0,FALSE,IF(L124,F ALSE,TRUE)))

--
Thanks
Shawn


"Ron Rosenfeld" wrote:

On Thu, 25 Feb 2010 10:49:01 -0800, Shawn
wrote:

Is there a way to have the cells validate to quarter units? Specifically, I
only want users to be able to enter .00; .25; .50; or .75 numbers.

Data/Validation/Settings

Custom
Formula: =MOD(A1,0.25)=0

--ron
.

Validate to quarter units
 

On Thu, 25 Feb 2010 12:51:02 -0800, Shawn
wrote:

I got the following to do just what I wanted (kicks out less than 0, more
than 24, and anything that isn't on a quarter unit).

=IF(MOD(L1,0.25)<0,FALSE,IF(L1<0,FALSE,IF(L124, FALSE,TRUE)))

--
Thanks
Shawn

Glad to help Shawn.

But adding the 0-24 constraint, this is a bit shorter:

=AND(A1=0,A1<=24,MOD(A1,0.25)=0)

--ron