Round up to a given value (2000)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

Thanks Hans & Steve for the quick reply, I am in retail and as such $10.99 is the choice to $11.00

Reply | Quote

Try:
=IF(INT(CEILING(A2,0.5))=CEILING(A2,0.5),CEILING(A2,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.

Reply | Quote

Nice. ,

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

also works.

Reply | Quote

Much neater!

Reply | Quote

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)

Reply | Quote

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?

Reply | Quote

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:

A1	=CEILING(A1,0.5)
10.12	10.5
10.51	11

Do you really want 10.99?

Reply | Quote

… 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),(ROUNDDOWN(C7,0)+0.99))

Reply | Quote