Ceiling & Floor function with constraints

[helenkim]

Hi All,
Need help formulating a formula, please. I want to use the CEILING and FLOOR function but have constraints.
I have a database of numbers and depending whether the number is +- 20% from a set value and its increments, I want it to perform either the CEILING or the FLOOR function.

For ex, I have the numbers 85. And a I have a set value of 30 or increments of 30 rather. If 85 is withing 20% of 90 (which is an increment of the set value 30), I want it to do the CEILING function and return me a value of 90. In this case, I know 85 is within the 20% of 90 so it should return 90.

However, if i had the number 65, the formula should return the value of 60, not 90, since, 65 is closer to 60 than to 90.

Does this make sense? Any ideas?

 

[VoG]

Take a look at MROUND - I think that this will do what you want.

 

[helenkim]

Thanks for teaching me a new function! but the MROUND function only takes the midpoint. I need it to be within 20%. Therefore I think I have to formulate and IF statement and then use the CEILING and FLOOR functions.

What do you think?