Round to up to a threshold

[jeffbert]

However, I need to set an upper limit on how high a ceiling function will
round.

For instance: h2= -180 and k2=24
=ceiling(ABS(H2),K2)

This formula would give me 192. However, I need a third varable as an upper
limit. For instance, I cannot have the answer from the previous equation go
above 100. Therefore, the answer to the equation would be 96.
=ceiling(ABS(H2),K2) where that answer is less than or equal to L2

Any and all help is apprecitated!

 

[Ron Coderre]

Try this:

=MIN(CEILING(ABS(H2),K2),100)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Elkar]

One of these two formulas should work for you, depending if you want L2
subject to the significance of K2 or not.

=MIN(CEILING(ABS(H2),K2),L2)

=MIN(CEILING(ABS(H2),K2),INT(L2/K2)*K2)

HTH,
Elkar

 

[Ron Coderre]

Actually, to have the formula max controlled by cell L2.....like you asked
for....


=MIN(CEILING(ABS(H2),K2),L2)
Where
H2 contains the source value
K2 contains the increment to calculate
L2 contains the upper limit of the calculation

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[jeffbert]

Thanks for the help, I used your second solution, in order to keep the
significance of K2. I appreciate the quick and accurate response.

 

[Ron Coderre]

I see that YOU read the entire post...
whereas, I apparently did not. :\

***********
Regards,
Ron

XL2002, WinXP

 

[jeffbert]

Ron

Thanks for the reply. I tried your solution, but it does not keep the
significance of K2. I used this solution from Elkar.

=MIN(CEILING(ABS(H2),K2),INT(L2/K2)*K2)