Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question re conditional responses
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question re conditional responses
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" -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Liz S." wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question re conditional responses
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 -- __________________________________ HTH Bob "Liz S." <Liz wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question re conditional responses
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 On Nov 26, 6:24*pm, Liz S. <Liz wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question re conditional responses
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. -- Rick (MVP - Excel) "Liz S." <Liz wrote in message ... 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question re conditional responses
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 "Liz S." wrote: 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question re conditional responses
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). -- Rick (MVP - Excel) "Shane Devenshire" wrote in message ... 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 "Liz S." wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
no responses... | Excel Discussion (Misc queries) | |||
How can I put in a control box in Excel for Yes/No responses? | Excel Discussion (Misc queries) | |||
counting responses - please help | Excel Worksheet Functions | |||
adding up yes, no, n/a responses | Excel Discussion (Misc queries) | |||
Charting survey responses | Charts and Charting in Excel |