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!

Try this:

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

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

XL2002, WinXP


"jeffbert" wrote:

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!

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


"jeffbert" wrote:

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!

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" wrote:

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!

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


"Elkar" wrote:

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


"jeffbert" wrote:

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!

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

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

XL2002, WinXP


"Elkar" wrote:

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


"jeffbert" wrote:

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

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)


"Ron Coderre" wrote:

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" wrote:

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!