Rounding UP/Down in excel

[ARF85]

Hello,

Say i have a price of 1015 and i want to round it up to 1049 and a price of say 1070 to 1099 how can i do this in a formula?

basically i want any number between 00 and 49 rounding to the nearest 49 and any number between 50 and 99 to the nearest 99.

Thanks

 

[baitmaster]

welcome to the board

try =ROUNDUP(A1/50,0)*50-1

 

[baitmaster]

Sorry, wrong answer for multiples of 50. Use this instead:
=ROUNDUP((A1+1)/50,0)*50-1

 

[anandvarma]

Try this

<table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 64px"> <col style="WIDTH: 64px"></colgroup> <tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td> </td> <td>A</td> <td>B</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</td> <td style="TEXT-ALIGN: right">1015</td> <td style="TEXT-ALIGN: right">1049</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</td> <td style="TEXT-ALIGN: right">1070</td> <td style="TEXT-ALIGN: right">1099</td></tr></tbody></table>
<table style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>B1</td> <td>=IF((RIGHT(A1,2)+0)<50,ROUNDDOWN(A1,-2)+49,ROUNDDOWN(A1,2)+99)</td></tr> <tr> <td>B2</td> <td>=IF((RIGHT(A2,2)+0)<50,ROUNDDOWN(A2,-2)+49,ROUNDDOWN(A2,-2)+99)</td></tr></tbody></table></td></tr></tbody></table>