Round up a range of numbers

[PTx]

Is there an easy way to round a range of numbers up? I have a big list of prices and I would like to keep the two digits after the decimal point as .00, but round up the main number. And I would like to do that without writing a formula in every cell. Is this possible?

[Paul]

How do you "round up" .00?

Are you trying to say you have numbers like: 4.69, 5.02, 13.45 and want them all rounded UP to the next whole number (5.00, 6.00, 14.00)? Or do you want to round normally (5.00, 5.00, 13.00)?

Please explain a little better, and add some examples.

[jasoncw]

If I understand correctly you want to round the integer portion up, and leave the fraction? (e.g. 152.26 > 200.26)

I don't think this is possible without either a formula for each cell, or via VBA.

[PTx]

How do you "round up" .00?

Are you trying to say you have numbers like: 4.69, 5.02, 13.45 and want them all rounded UP to the next whole number (5.00, 6.00, 14.00)? Or do you want to round normally (5.00, 5.00, 13.00)?

Please explain a little better, and add some examples.

I'm sorry I didn't explain better; I'm new to Excel and don't know much about it. The example you gave is exactly what I want to do. I've got a bunch of prices like $103.35, $75.86 and I would like to round them all up to the nearest dollar.

[PTx]

Here is a better example: I have two columns of prices:

$265.74 $358.44
$286.34 $386.25
$286.34 $386.25
...

And I would like them to be rounded up the nearest dollar like so:

$266.00 $359.00
$287.00 $387.00
$287.00 $387.00

There are a lot of prices, and I would like to be able to select them all and round them up all at once, if at all possible.

[jasoncw]

Use

=CEILING(A1,1)

HTH

Jason

[PTx]

Use

=CEILING(A1,1)

HTH

Jason

Thanks! What do I use it on and how do I use it? And can I use it on all my prices at once?

[jasoncw]

Oh, yeah, I forgot you wanted to do them all at once. Using the formula, you would need to do this for each cell. The only way to do this all at once is with VBA (macro).

[starguy]

Thanks! What do I use it on and how do I use it? And can I use it on all my prices at once?

if your data in is col A and starts from cell A1 downwards.
put above formula in cell B1 and copy it down. it will show you the rounded values in col B.

[PTx]

if your data in is col A and starts from cell A1 downwards.
put above formula in cell B1 and copy it down. it will show you the rounded values in col B.

Thank you for the clarification. I followed these instructions, but now I have extra columns that I don't want. Is it possible to just change the original values and not make another column, row, etc.?

I looked into VBA, but it seems a bit too complex for my limited Excel skills.

[jasoncw]

Select all cells with the CEILING function. Copy (CTRL + C), then select Edit > Paste Special > Values. You can then remove/delete the original cells, leaving only the rounded values.

[PTx]

Select all cells with the CEILING function. Copy (CTRL + C), then select Edit > Paste Special > Values. You can then remove/delete the original cells, leaving only the rounded values.

Awesome! Thank you VERY much! This should do the trick.

Thanks again!

[jasoncw]

No problem.