Rounding Numbers

[dave_br]

Is it possible to round a number that is the result of a sum to the nearest .99??

i.e. 2.25*2=£4.50 can this be rounded up to £4.99 by means of a rounding formula?

thanks in advance!

dave

 

[MrKowz]

Try:

=ROUNDUP(A1,0)-0.01

 

[dave_br]

perfect that works great, can it also round up to the nearest whole pound that ends in 9? only on values >100

i.e. 109, 119 129 etc?

 

[MrKowz]

Try:

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">95.32</td><td style="text-align: right;;">95.99</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">100</td><td style="text-align: right;;">109.99</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">101</td><td style="text-align: right;;">109.99</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">102</td><td style="text-align: right;;">109.99</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">103</td><td style="text-align: right;;">109.99</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">104</td><td style="text-align: right;;">109.99</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">105</td><td style="text-align: right;;">109.99</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">106</td><td style="text-align: right;;">109.99</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">107</td><td style="text-align: right;;">109.99</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">108</td><td style="text-align: right;;">109.99</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">109</td><td style="text-align: right;;">109.99</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">110</td><td style="text-align: right;;">119.99</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">111</td><td style="text-align: right;;">119.99</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">112</td><td style="text-align: right;;">119.99</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">113</td><td style="text-align: right;;">119.99</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">114</td><td style="text-align: right;;">119.99</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">115</td><td style="text-align: right;;">119.99</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">116</td><td style="text-align: right;;">119.99</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">117</td><td style="text-align: right;;">119.99</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">118</td><td style="text-align: right;;">119.99</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">119</td><td style="text-align: right;;">119.99</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">120</td><td style="text-align: right;;">129.99</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">121</td><td style="text-align: right;;">129.99</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">122</td><td style="text-align: right;;">129.99</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1 (2)</b></th></tr></td></thead></table><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">L6</th><td style="text-align:left">=IF(<font color="Blue">K6>=100,ROUNDUP(<font color="Red">K6+1,-1</font>),ROUNDUP(<font color="Red">K6,0</font>)</font>)-0.01</td></tr></tbody></table></td></tr></table><br />