Excel: How do I tell a range of cells to roundup?

Factual Questions
1

First I know how to use the ROUNDUP function.

My problem is being able to tell a range of cells to ROUNDUP. I have dozens of cells I need to do this too and really do not want to type it all in one at a time.

So, I have:

=SUM(A1:A20)

I want:

=ROUNDUP(SUM(A1:A20),-2)

And I need the above done to numerous cells. Unfortunately I cannot do a drag copy since the next cell in the row is something like “=SUM(L23:L45)”. To drag copy would increment the cell names wrong and screw it up.

Any way to do this?

2

How about this? Sort such that all cells to be rounded are together Drag the ROUNDUP from the cell that has it to the other cells. Re-sort to the original format.

3

You could use search and replace with a wildcard, but you’ll have to implement some VB code. See here:

http://excel.tips.net/Pages/T003303_Wildcards_in_Replace_With_Text.html

4

After I wrote my post I guessed someone would come up with the sensible solution.

Unfortunately for the post I simplified how my sheet is setup to keep what I was after simple.

Basically I have a grid with one thing along the X axis and another on the Y. It is a big grid and relevant numbers are shotgun scattered all in that thing. There really is no simplifying that.

So, in the next sheet I am saying =Data!L66 then the next cell is =Data!M69 and so on. Just no good way to get them all in a nice line.

5

The programs I write require that data be in fixed-position text files. Some data comes in in ‘print image’ Excel files (or text, but let’s forget that for here). ‘Print image’ files look like a report print-off instead of a spreadsheet. Some data is set up so that I can look for certain things and get rid of a lot of the ‘clutter’, leaving only the data I need. Other data isn’t so nice. Of course sorting can be a problem, as you need to get it back to the way it was.

To return to the original layout, I insert a new Column A and insert line numbers. (Remembering to copy and paste as text so they don’t change.) That way I can sort and sort and eventually get parts together. Sometimes it’s a real PITA, but it can be done.

6

It can be done with 3 search and replace operations:

replace =SUM with ROUNDUP(SUM (note there is no equal sign)
replace ) with ),-2)
replace ROUNDUP with =ROUNDUP

7

Interesting idea…I’ll give it a go.

Always thought Search/Replace looked at data displayed and not formulas. No good reason for thinking that…one of those dumb assumptions.

8

That’s what I was missing. I tried doing a regular S&R, but kept getting a formula error. Nicely done.

Actually, it can be done either way. Look in the Options section of the S&R panel.

9

What are you doing with the cells? For some purposes, simply changing their formatting to the desired number of decimal places works. Again, this is a limited solution for limited situations, but it’s an option.

10

Well from his formula, he’s trying to round up to the next even hundred. I don’t think you can do that with formatting.

11

oops, didn’t see the **-**2.

12

You’re right. One can try formatting with scientific notation (“0E+02”) to the closest even hundred but it rounds up and down instead of just up.

13

Just make sure you understand the difference between rounding a sum versus rounding the values which go into a sum. Also the difference between rounding a cell’s value versus formatting the cell’s display to show an unrounded value as a rounded display.

There are a lot of combinations there and if you’re not clear on the differences and you don’t pick the correct one for your needs you’re gonna create a GIGO situation.

14

I hear ya and am careful since I know rounding can compound problems down the line.

In this case I have inputs of varying numbers but I must buy in lots of 100. So, even if the number is 201 I need 300 or will run short (I can only use 100 or 200 or 300 and so on). Yes that may seem wasteful but the remaining 99 are left in the pot and accounted for in the next run or two (essentially I can buy less somewhere down the road because I have a stock on hand). In the end it self adjusts.