Excel specialists, HELP! **NEW QUESTION, 11/18/04**

[codeyf]

Ok, so I know you can create a validation "rule" that limits the input in to a cell(s).

Now, is it possilbe to limit part of that input?

For example, the number entered should go to just 2 decimal points (easy), AND the integer should only end it either .00, .25, or .50 (ie 543574684.25, 1.5, etc etc etc)

Is it possible? Short of entering in EVERY integer in .25 intervals?


UPDATE/NEW QUESTION 11/18/2004

So after all that shenanigans, I took it to the client who then said:

I want to be able to ROUND numbers, already IN the spread sheet, to the closest number divisible by .25.

Example:

Cell A1 has the value 5.68, A2 has 3.04, A3 has 6.46 etc etc etc. Can I select all the cells, execute a function/formula that will round the numbers to 5.75, 3.00, and 6.50 etc etc.

 

[codeyf]

Anyone?

 

[noxxic]

MOD(A1,0.25)=0 I guess would work.

 

[NeoV]

I think you are going to need a second cell with a formula in to do it

I'm not familiar with the "mod" command, but ceiling would work as well

CEILING(a1,.25), but that is sort of cheating since you technically aren't limiting the data entry part....try posting this question over at MrExcel dot com.

 

[NeoV]

ok, more info..

change your validation to "Custom", and we'll need a formula to do the trick...

I've got a formula here, that I got from Mr.Excel a week ago, that does something similar...

"=AND(AND($I2>0,$I2<999999,$I2<=$H2),IF($H2<>"",$I2<=$H2=TRUE))"

What that does is make sure that the value in I2 is less than or equal to H2, is greater than zero, and is less than 999999, as long as H2 isn't blank...

maybe we can work that mod statement into the 2nd half....let me play with it a bit...


DOH, Noxxic has the formula we need...

so change the validation type for the cell to "custom" and put his formula in there, =mod(a2.25)=0

all set

 

[codeyf]

Whoa! Ok, I'm lost....shows how little I know about Excel. I just had a help desk ticket come thru asking if this was possible......

I have a feeling that the user however is not looking to get this complicated....

 

[codeyf]

Originally posted by: NeoV
ok, more info..

change your validation to "Custom", and we'll need a formula to do the trick...

I've got a formula here, that I got from Mr.Excel a week ago, that does something similar...

"=AND(AND($I2>0,$I2<999999,$I2<=$H2),IF($H2<>"",$I2<=$H2=TRUE))"

What that does is make sure that the value in I2 is less than or equal to H2, is greater than zero, and is less than 999999, as long as H2 isn't blank...

maybe we can work that mod statement into the 2nd half....let me play with it a bit...


DOH, Noxxic has the formula we need...

so change the validation type for the cell to "custom" and put his formula in there, =mod(a2.25)=0

all set

ok...i'll see if I can figure out where on his sheet that should go!

Thanks man!

 

[NeoV]

Codey, it's really not that bad

Lets say the cell in question is cell A1.

Put the cursor in the cell, and goto Data-Validation

Change the Allow value to Custom, and then in the formula box, just type in, without the quotes, "=mod(a1,.25)=0"


That's it

You can also modify the error msg if you feel that is necessary

 

[codeyf]

Hmmm...get an error message when I put that in there:

The formula you typed contains an error.

- For info about fixing common formula problems, click Help.
- To get assistance in etering a function, click OK, then click Function on the Insert menu.
- If you are not trying to enter a formula, avoid using the equal sigh (=) or the minus sign (-), or preced it with a single quotation mark (').

[ ok ] [ help ]

 

[codeyf]

ok, will try it with the quotations!

 

[codeyf]

Well, I input the formula and then tried to input the data. Several tries with random numbers at intervals of .25 and I get the error message saying invalid entry.

 

[codeyf]

What about saying that the data input must be DIVISIBLE by .25? Would that work? And how would you type out that formula?

 

[NeoV]

that is what the mod formula does...it's working fine for me..check your formula again

make sure you have a comma and then a period in the mod formula...just copy and paste this..

=mod(a1,.25)=0

 

[yuchai]

He had the right idea. But he got it the other way around.
It should be =NOT(MOD(A1,0.25))

 

[codeyf]

Hmm. Ok it takes the formula, but it still allows me to enter in values not divisible by .25.

 

[codeyf]

doh, i got it. wasn't changing the cells in the formula...

:beer:'s for you guys!

 

[codeyf]

Bump for a new question!

 

[Concillian]

I don't think you can do it without a macro.

However I cooked up a quick macro that seems to work. I am not a VB expert, so it can probably be done more cleanly than I have done, but as I said, it works.

To make it faster add:

"application.screenupdating = false"

just after the definitions and

"application.screenupdating = true"

just before the "End Sub"

 

[noxxic]

Originally posted by: yuchai
He had the right idea. But he got it the other way around.
It should be =NOT(MOD(A1,0.25))

To clarify... MOD(X,Y) returns "the remainder of X divided by Y." It's not a logical true/false function. Although NOT() works, it's because it's the same as "=FALSE" and FALSE is the same as 0.