Results 1 to 10 of 10
Thread: Round up to a given value (2000)

20020820, 14:58 #1
 Join Date
 Jan 2001
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20020820, 15:29 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20020820, 15:30 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
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?

20020820, 15:45 #4
 Join Date
 Jan 2001
 Posts
 8
 Thanks
 0
 Thanked 0 Times in 0 Posts
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>

20020820, 15:54 #5
 Join Date
 Aug 2001
 Location
 Frederick, Maryland, USA
 Posts
 1,788
 Thanks
 0
 Thanked 2 Times in 2 Posts
Re: Round up to a given value (2000)
... and yet another option if the values to be rounded are in cell c7
IF((C7ROUNDDOWN(C7,0))<0.51,(ROUNDDOWN(C7,0)+0.5),(ROUND DOWN(C7,0)+0.99))Regards,
Gary
(It's been a while!)

20020820, 15:56 #6
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,298
 Thanks
 3
 Thanked 202 Times in 187 Posts
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.Regards,
Rory
Microsoft MVP  Excel

20020820, 16:59 #7
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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.John ... I float in liquid gardens
UTC 7ąDS

20020821, 09:01 #8
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,298
 Thanks
 3
 Thanked 202 Times in 187 Posts
Re: Round up to a given value (2000)
Much neater! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
Regards,
Rory
Microsoft MVP  Excel

20020829, 05:32 #9
 Join Date
 Aug 2002
 Location
 Boise, Idaho, USA
 Posts
 38
 Thanks
 0
 Thanked 0 Times in 0 Posts
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)

20020829, 14:00 #10
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
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?John ... I float in liquid gardens
UTC 7ąDS