Data Validation...A Multiple of a number

[Dean]

How do you set the data validation formula to limit entry to a multiple
of a number. My specific need is to have users enter only numbers that
are multiples of 50. I've been using a drop down list but the range is
getting really long now and it seems to me there must be an easier way
than a long drop down box. Anyone know how to create a formula from/in
the data validation options?
Dean

[colofnature]

In the validation criteria, select Custom from the Allow list, and enter

=MOD(A1,50)=0

in the Formula box (change A1 to whichever cell you're putting the validation on). If a user tries to enter a value which isn't a multiple of 50 they'll get an error.

Hope that helps
Col

[Bernie Deitrick]

Dean,

Use Data / Validation... and choose "Custom" and use the formula:

=INT(A1/50)=A1/50

Replace the A1 with the current active cell's address.

HTH,
Bernie
MS Excel MVP


"Dean" <[email protected]> wrote in message
news:[email protected]...
> How do you set the data validation formula to limit entry to a multiple
> of a number. My specific need is to have users enter only numbers that
> are multiples of 50. I've been using a drop down list but the range is
> getting really long now and it seems to me there must be an easier way
> than a long drop down box. Anyone know how to create a formula from/in
> the data validation options?
> Dean
>

[Dean]

Thank you! Perfect. All set.
Dean



colofnature wrote:
> In the validation criteria, select Custom from the Allow list, and
> enter
>
> =MOD(A1,50)=0
>
> in the Formula box (change A1 to whichever cell you're putting the
> validation on). If a user tries to enter a value which isn't a multiple
> of 50 they'll get an error.
>
> Hope that helps
> Col
>
>
> --
> colofnature
> ------------------------------------------------------------------------
> colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
> View this thread: http://www.excelforum.com/showthread...hreadid=558426

[Aharon]

Hi!
I got here by googling (excel cell validation formula "multiple of a number").
As a total layman on Excel (let alone any kind of "programming"), I was proud to figure out the word "validation" for a field. I felt smart for a couple seconds!
The reply you gave "(A1,50)=0 seems to be what I need, but I do not know how or where to use it on the spreadsheet I am trying to assemble.
My problem is: I have master cartons for my products, which acommodate 2, 4 or 6 units, and I need my clients to choose only multiples of these numbers.
Will you please be very patient and explain as you would to an 8 years old kid? Please?
Thanks, Aharon

[johnjohns]

Hi Aharon,
Welcome to this forum. Please read the forum rules, before posting. This post is not in accordance with Rule2 given below.

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

[Aharon]

Hi Johnjohn, point taken.
Incidentally, I came back just to say that I have figured how to use the answer posted here "=mod(cell,x)=o" : in a separate column, I am getting a "true" or "false" response. I added an explanation for the "false" reading on the column header.
Thanks!

[S.A.Rafay]

Dear Team I want to add a value in a cell which should be the power of 2 .
If i put 3 in cell it would turn in to 8 , if I put 4 it should display 16 in the same cell

[Glenn Kennedy]

Hi.

It's a forum rule that you start your own thread, rather than "piggy-backing" on someone else's thread. This helps prevent massive confusion arising - which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution).

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures. So, prepare a SMALL sample sheet (10-20 rows, not thousands!!!). Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!


The yellow banner about sample worksheets, at the top of the screen tells you how to post a sheet here on the Forum.