Need help formulating a hard formula involving CEILING & FLOOR & IF statement

[helenkim]

Hi all,
I posted a couple of days ago but didn't get much help so trying again...

I have a row of numbers. For each number, I need to round up to the nearest 30 UNLESS its within 20% of the nearest 30 increment, in which case I need it to round down.

Lets say I have the number 85 and 32. The formula should return a value of 90 for the number 85, and a value of 30 for the number 32 since 32 is within 20% of 30.

So any numbers between 0-36 (20% of 30 is 6 so 30+6=36) should return a value of 30 and any numbers between 37-72 should return 60. (Since 20% of 60 is 12, so 12+60=72).

Can anyone help, please?!??

 

[Marcelo Branco]

Hi,

Not sure if i understood correctly, but maybe (assuming the number in A1)

=IF(A1<=INT(A1/30)*30*1.2,FLOOR(A1,30),CEILING(A1,30))

HTH

M.

 

[markmzz]

Or try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Number</td><td style="font-weight: bold;text-align: center;;">Next</td><td style="font-weight: bold;text-align: center;;">Bottom</td><td style="font-weight: bold;text-align: center;;">Top</td><td style="font-weight: bold;text-align: center;;">Round 30</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">85</td><td style="text-align: center;;">60</td><td style="text-align: center;;">48</td><td style="text-align: center;;">72</td><td style="text-align: center;;">90</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">37</td><td style="text-align: center;;">30</td><td style="text-align: center;;">24</td><td style="text-align: center;;">36</td><td style="text-align: center;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">65</td><td style="text-align: center;;">60</td><td style="text-align: center;;">48</td><td style="text-align: center;;">72</td><td style="text-align: center;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">32</td><td style="text-align: center;;">30</td><td style="text-align: center;;">24</td><td style="text-align: center;;">36</td><td style="text-align: center;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">48</td><td style="text-align: center;;">30</td><td style="text-align: center;;">24</td><td style="text-align: center;;">36</td><td style="text-align: center;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">72</td><td style="text-align: center;;">60</td><td style="text-align: center;;">48</td><td style="text-align: center;;">72</td><td style="text-align: center;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">24</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">0</td><td style="text-align: center;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">36</td><td style="text-align: center;;">30</td><td style="text-align: center;;">24</td><td style="text-align: center;;">36</td><td style="text-align: center;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">100</td><td style="text-align: center;;">90</td><td style="text-align: center;;">72</td><td style="text-align: center;;">108</td><td style="text-align: center;;">90</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">110</td><td style="text-align: center;;">90</td><td style="text-align: center;;">72</td><td style="text-align: center;;">108</td><td style="text-align: center;;">120</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">130</td><td style="text-align: center;;">120</td><td style="text-align: center;;">96</td><td style="text-align: center;;">144</td><td style="text-align: center;;">120</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">145</td><td style="text-align: center;;">120</td><td style="text-align: center;;">96</td><td style="text-align: center;;">144</td><td style="text-align: center;;">150</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">160</td><td style="text-align: center;;">150</td><td style="text-align: center;;">120</td><td style="text-align: center;;">180</td><td style="text-align: center;;">150</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">181</td><td style="text-align: center;;">180</td><td style="text-align: center;;">144</td><td style="text-align: center;;">216</td><td style="text-align: center;;">180</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Round30</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=INT(<font color="Blue">A2/30</font>)*30</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=B2*0.8</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=B2*1.2</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=IF(<font color="Blue">A2=0,30,IF(<font color="Red">A2<=(<font color="Green">INT(<font color="Purple">A2/30</font>)*36</font>),INT(<font color="Green">A2/30</font>)*30,INT(<font color="Green">A2/30</font>)*30+30</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[helenkim]

THANK YOU SO MUCH! that is EXACTLY what i was looking for. I eventually came up with option 2 that you provided, but i wanted option 1, just one formula in once cell.

THANK YOU AGAIN!!!

 

[Marcelo Branco]

THANK YOU SO MUCH! that is EXACTLY what i was looking for. I eventually came up with option 2 that you provided, but i wanted option 1, just one formula in once cell.

THANK YOU AGAIN!!!

You are welcome and tks for the feedback

M.
ps: not sure if you chose my solution or the Markmzz's solution. But what is important is that you have your problem solved

 

[helenkim]

oh, i took your solution, Marcelo. Thank you, both!
but quick question, if i wanted the option to leave the 20% a flexible number, then i can have the number "1.2" in the formula reference a cell that says 20%, right? So this way, i have the option to change it to 30% or 40%. all i need to do is replace the 1.2 with a cell reference and write 20% in the cell, correct?

Thank you aqgain!!

 

[markmzz]

THANK YOU SO MUCH! that is EXACTLY what i was looking for. I eventually came up with option 2 that you provided, but i wanted option 1, just one formula in once cell.

THANK YOU AGAIN!!!

Helenkim,

No problem, but I use only one formula. Only this below in E2:

=IF(A2=0,30,IF(A2<=(INT(A2/30)*36),INT(A2/30)*30,INT(A2/30)*30+30))

The others formulas (B2, C2 and D2) are there only for verification.

Finally, as the Marcelo has already said, the important thing is that your problem has been resolved.

Markmzz

 

[Marcelo Branco]

oh, i took your solution, Marcelo. Thank you, both!
but quick question, if i wanted the option to leave the 20% a flexible number, then i can have the number "1.2" in the formula reference a cell that says 20%, right? So this way, i have the option to change it to 30% or 40%. all i need to do is replace the 1.2 with a cell reference and write 20% in the cell, correct?

Thank you aqgain!!

Correct! Change 1.2 to a cell-reference

M.

 

[markmzz]

Another thing is if you want a flexible number for % in the formula, you can use this (i.e. 30%)

type 1.3 in F1 and use the formula below.

=IF(A2=0,30,IF(A2<=(INT(A2/30)*30*$F$1),INT(A2/30)*30,INT(A2/30)*30+30))

Markmzz