Round up or down (based on input)

[niravrph]

Hi,

I would like to know how to set up Excel to limit an user from inputting something that is not divisible by 50. For example, I am setting up an order form for colleagues to order some educational fact sheets. These sheets are printed in packs of 50 - so they would have to order quantities in numbers divisible by 50. I would like to inform them to order in multiples of 50 only * AND * automatically round anything they input EITHER up or down by 50. That is, if they enter 418 it rounds down to 400, if they enter 469 it rounds up to 500.

Can you help me?

Thank you!
Nirav

 

[Jonmo1]

Welcome to the board..

Try

=ROUND(A1/50,0)*50

 

[niravrph]

Thank you, Jon. I truly appreciate the speedy response. Sorry to be slow on the uptake but will this formula automatically round based on the user's input? Should I be entering this formula using the Validation function? I have several rows with 5 cells per row that users can input quantities into. I want to be able to round all of these rows.

Thank you,
Nirav

 

[Jonmo1]

It won't do it on the cell the user inputs into.

say the user inputs into A1
Put that formula in B1
B1 will show the rounded result of the user's input in A1.

And you can copy B1, then paste it onto B2 through B(whatever)

 

[niravrph]

Thank you, Jon. I REALLY appreciate your help on this!

 

[West Man]

If you want to limit them to multiples of 50 rather than rounding, you may wish to use data validation with a custom formula of =MOD(A2,50)=0 where A2 is the input cell.

 

[niravrph]

West Man --- you're the Man! Thank you!!!!!
Yeah baby, I'm cooking with gas now!!
LOL

- Nirav

 

[West Man]

Glad to be of assistance.