# Thread: Round up to a given value (2000)

1. ## Round up to a given value (2000)

I need help in assigning a round up to a range of cells ex. if a value is \$10.51 I would like to round up to \$10.99 and if the value is \$10.12 round it up to \$10.50. the column will have various values.

2. ## Re: Round up to a given value (2000)

I wasn't clear on all the rounding since you rounded up to 10.99 and not 11.00

=IF(INT(A1*2)=A1*2,A1,ROUND(A1+0.5,0)-IF(INT(A1+0.5)=ROUND(A1+0.5,0),0.01,0.5))
will give you:

x.00 = x.00
x.01 to x.5 = x.5
x.51 to x.99 as x.99

Steve

3. ## Re: Round up to a given value (2000)

Excel has a Ceiling function that rounds up to the next higher multiple of a given number. It seems you want to round in multiples of 0.5. But that will not round to 10.99:

<table border=1><td valign=bottom>A1</td><td valign=bottom>=CEILING(A1,0.5)</td><td align=right valign=bottom>10.12</td><td align=right valign=bottom>10.5</td><td align=right valign=bottom>10.51</td><td align=right valign=bottom>11</td></table>

Do you really want 10.99?

4. ## Re: Round up to a given value (2000)

Thanks Hans & Steve for the quick reply, I am in retail and as such \$10.99 is the choice to \$11.00 <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

5. ## Re: Round up to a given value (2000)

... and yet another option if the values to be rounded are in cell c7

IF((C7-ROUNDDOWN(C7,0))<0.51,(ROUNDDOWN(C7,0)+0.5),(ROUND DOWN(C7,0)+0.99))

6. ## Re: Round up to a given value (2000)

Try:
=IF(INT(CEILING(A2,0.5))=CEILING(A2,0.5),CEILING(A 2,0.5)-0.01,CEILING(A2,0.5))
Note: if you could have 0, you'll need to tweak it a bit to avoid -0.01 as an answer!
Hope that helps.

7. ## Re: Round up to a given value (2000)

Nice. <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>,

=CEILING(A3,0.5)-NOT(MOD(CEILING(A3,0.5),1))*0.01

also works.

8. ## Re: Round up to a given value (2000)

Much neater! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

9. ## Re: Round up to a given value (2000)

JohnBF's code very cleverly takes advantage of the fact that TRUE =1 and FALSE =0. But it still leaves the problem of rounding to -\$0.01 if the input is zero. We can fix that by using the IF() function. Note that FLOOR(..., 0.01) would give a numeric error, because the first argument is negative and the second argument is positive. To round the contents of cell A1 (to positive values or zero), use

=IF(A1<0.01,0,CEILING(A1,0.5)-NOT(MOD(CEILING(A1,0.5),1))*0.01)

But, dmerrix, why do you want to round to the nearest half dollar instead of to 49 cents? In the latter case, the code is a bit simpler, and you could round the contents of cell A1 using

=IF(A1<0.01,0,CEILING(A1,0.5)-0.01)

10. ## Re: Round up to a given value (2000)

or we can use =MAX() to set a minimum value, in this case zero:

=MAX(0,CEILING(A1,0.5)-NOT(MOD(CEILING(A1,0.5),1))*0.01))

Since it was a retail goods situation, Rory and I assumed there wouldn't be any freebies! But I also wondered about your question, why not "KMART" everything (as we sometimes say in the US) at the nearest nine cents?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•