• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

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

codeyf

codeyf

Lifer
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.
 
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.

 
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
 
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....
 
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
Click to expand...

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

Thanks man!
 
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
 
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 ]
 
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.
 
What about saying that the data input must be DIVISIBLE by .25? Would that work? And how would you type out that formula?
 
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

 
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"
 
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))
Click to expand...

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.
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top