Round to up to a threshold
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! |
Round to up to a threshold
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! |
Round to up to a threshold
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! |
Round to up to a threshold
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! |
Round to up to a threshold
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! |
Round to up to a threshold
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! |
Round to up to a threshold
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! |
All times are GMT +1. The time now is 09:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com