I want to create a cell in and Excel spreadsheet that can only have Even numbers entered into it and also one just for Odd numbers. Any ideas what formula I can put into the Data Validation formula to be able to do this. Thank you.
-
7Something in Excel?– jonscaJun 16, 2011 at 20:33
-
since people are commenting that the topic is not clear, even though the tag says data-validation, you might add excel into the question, if it is an excel question.– datatooJun 16, 2011 at 21:04
-
2Voting to reopen since he clarified the question– Darth AndroidJun 16, 2011 at 21:44
-
Divide each value by two and if there is a remainder you know it is Odd, if no remainder, even.– music2myearJun 16, 2011 at 21:46
Add a comment
|
2 Answers
In the cells you want to check for even set the data validation to custom, then in the formula
=NOT(MOD(A2,2))
check odd numbers in the same way with the formula
=MOD(A3,2)
of course adjusting the cell address, the style, and Error Alert you want in response
Here's an alternative that uses Excel's Odd and Even functions. Assuming you are validating for even in A2 and Odd and A3. Enter these as Custom formulas in the Data Validation dialog:
=A2=EVEN(A2)
=A3=ODD(A3)
-
these do produce odd or even results, but change them for the user, rather than demanding they enter either and odd or even value. perhaps that is enough for the situation, but seems error prone– datatooJun 18, 2011 at 16:00
-
@ datatoo I've edited my answer to clarify that these would be entered as Custom formulas in the Data Validation dialog. I thought that was assumed since it's what the OP asked for. Jun 19, 2011 at 2:02
-