Question re conditional responses

[Liz S.]

Can I set up a conditional response that posts a false message if the user
enters a value that is not divisable by 4?

I am creating a cost calulator and the user must enter a number of pages but
the number of pages must be a multilpe of 4. Minimun answer is 4.

 

[Michael]

You could use data validation,
From the main menu select:
Data -> Validation
From the validation criteria dropdown select List, in the blank box
(Source), key in the numbers 1 through 4 separated by commas, then you can
set up a message on the Error Alert tab, something like:
"Only values between 1 and Four are allowed"

 

[Bob Phillips]

You could use Data Validation with a type of Custom and a formula of

=MOD(E2,4)=0

where E2 is the cell in question

 

[Pete_UK]

Assuming that you enter the number of pages in A1, then you can put
this formula in B1:

=IF(MOD(A1,4)>0,"Not divisible by 4","ok")

Hope this helps.

Pete

 

[Rick Rothstein]

Select all the cells you want to have this functionality and note which cell
in the selection remains active (it will be the cell that is white, that is,
non-shaded). Click Data/Validation on Excel's menu bar, select "Custom" in
the "Allow" drop down box and put this formula in the "Formula" field...

=MOD(A1,4)=0

Substitute the address for the active cell from your selection in place of
the example A1 that I used in the above formula and make sure the "Ignore
blank" checkbox has a check mark in it. You can show a message when one of
the cells is selected by the user (see Input Message tab) if desired and you
can customize its message as well as the message when an incorrect entry is
inputted (see the Error Alert tab) or use the system's default message (your
choice). Click OK when you are done and the cells you initially highlighted
will now only allow a type in that is a multiple of 4.

 

[Shane Devenshire]

Hi,

All numbers are divisable by 4.

If you mean, as everyone is assuming, that is exactly divisable by 4 without
a remainder, then

=ROUND(MOD(A1,4),0)=0

This modification is necessary because MOD may return 0.000000000001 and
thus return the wrong result.

This formula goes into the Custom area of Data,Validation.
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Rick Rothstein]

Just out of curiosity, do you have an example of this (using a modulus of
4)? I would have figured that divisions (what MOD is doing underneath) by a
power of two would be immune from this problem (given the binary nature of
integer storage in a computer).