Round a Column of Numbers to a Specific List

**BCossette** · 08-07-2011, 09:27 PM

Hey All,

In column E I have a list of numbers. I want to round all of them to the nearest number in the following list:

400
600
800
1000
1200
1400
1600
1800

The closest I can find is this example, but I really am stumbling on where to start with it:
http://excel.bigresource.com/Track/excel-bQaNOgRp/

I dont need an error dialog box either for simplicity.

If there's a way to do this with a regular macro set of actions great, but if not if you could help me do it via VBA i can work that too.

I will most definately give good ratings to ANYONE that may be able to help me.

Thanks!

- Brandon

**gjlindn** · 08-07-2011, 10:06 PM

Could you use the MROUND function? It looks like your list is incremented by 200. So if your data is in column A it would be:

Please Login or Register  to view this content.

and if you need it to never be below 400 you could use:

Please Login or Register  to view this content.

**BCossette** · 08-07-2011, 11:02 PM

Originally Posted by gjlindn

Could you use the MROUND function? It looks like your list is incremented by 200. So if your data is in column A it would be:

Please Login or Register  to view this content.

and if you need it to never be below 400 you could use:

Please Login or Register  to view this content.

Is a2 only going to round column a row 2 or does that do the entire column? I guess ill go try this (Im on my android right now). Ill get back to you soon and give you some positive feedback if it works!

**Haseeb Avarakkan** · 08-07-2011, 11:05 PM

Or,

Consider A:A is the list of numbers, E:E is the actual numbers, So try this.

=SMALL(A:A,MIN(COUNT(A:A),COUNTIF(A:A,"<="&E1)+1))

**gjlindn** · 08-07-2011, 11:09 PM

Just for kicks I also created this UDF which will also do what you're looking for but is not limited to a static increment.

Please Login or Register  to view this content.

If your list is in $F$2:$F$9 and your target is in A2 then:

Please Login or Register  to view this content.

**BCossette** · 08-07-2011, 11:14 PM

Let me try to explain a little better:

I have a list of numbers down Column E. I want them all calculated to overwrite into Column E. Increments of 200. I need a script that will do the entire column.

If someone can get a little more exact on how to help i'd appreciate it. I have given good reputation to everryone that has responded.

**gjlindn** · 08-07-2011, 11:19 PM

@Haseeb A

=SMALL(A:A,MIN(COUNT(A:A),COUNTIF(A:A,"<="&E1)+1))

This is very nice, but appears to be always round up.

@BCossette

Is a2 only going to round column a row 2 or does that do the entire column?

MROUND is a formula you'd put into another cell...so it doesn't change the value of A2. You'd have to copy the formula down in order to apply to other cells. The same is true for the UDF i provided. If you want a macro to replace values I could easily modify the UDF to be a sub routine that does that. Thanks!

**BCossette** · 08-07-2011, 11:36 PM

Originally Posted by gjlindn

Could you use the MROUND function? It looks like your list is incremented by 200. So if your data is in column A it would be:

Please Login or Register  to view this content.

and if you need it to never be below 400 you could use:

Please Login or Register  to view this content.

Im getting a circular reference error and it automatically makes the number in the column 0. Im using the following code in just one cell (E7) to try and get this to work:

=MROUND(E7,200)

**BCossette** · 08-07-2011, 11:37 PM

Originally Posted by gjlindn

@Haseeb A This is very nice, but appears to be always round up.

@BCossette MROUND is a formula you'd put into another cell...so it doesn't change the value of A2. You'd have to copy the formula down in order to apply to other cells. The same is true for the UDF i provided. If you want a macro to replace values I could easily modify the UDF to be a sub routine that does that. Thanks!

Could you alter the UDF for me? Also ive never used a UDF, i've only done basic VBA code. I'm not sure where to integrate the two separate codes you have given me.

Thanks again guys.

**gjlindn** · 08-08-2011, 12:13 AM

A UDF is just a function put into a standard Module. Once you've done that, you can use the function just like you would any a standard Excel function (by typing it into a worksheet cell). You can input the following code to a standard or worksheet module and then call the RoundMe routine from the Macros button on the Devoloper tab. Remember to change the ranges in the code to your own.

Please Login or Register  to view this content.

**BCossette** · 08-08-2011, 12:18 AM

Im sorry if im just being a nitpick at this point:

How do I alter the code so that it does the entire column? There's always going to be a different amount of column values with each spreadsheet im doing this to.

Can you integrate the desired numbers to be specified into the subroutine? It would look much better than having a random string of numbers laying around on the spreadsheet.

Edit: I got this fully functional minus the above alterations needed. Appreciate all the help thus far guys.

**gjlindn** · 08-08-2011, 12:43 AM

Sure...here you go:

Please Login or Register  to view this content.

You can change/add numbers in the array right in the code now.

**BCossette** · 08-08-2011, 01:10 AM

Originally Posted by gjlindn

Sure...here you go:

Please Login or Register  to view this content.

You can change/add numbers in the array right in the code now.

PERFECT ! ! !

I have a few more questions related to my little program im making, but i'll mark this resolved and make new topics. Thanks again everyone.

**BCossette** · 08-08-2011, 11:51 AM

gjlindn,

Can you look at my program and tell me what i'm doing wrong? It worked at first, but now i'm getting a Run Time Error 13: Type Mismatch. I've tried everything and can't figure out what i'm doing wrong.

The file is too big to upload here, so download it from my web server:

http://www.1badz71tahoe.webs.com/427VEFixerv121.xlsm

THANKS!

- Brandon

**gjlindn** · 08-08-2011, 12:03 PM

Hi Brandon,

Change this

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

The "2" represented the first row of data you want to change. In your workbook it starts in row 7. Thanks!

**BCossette** · 08-08-2011, 01:18 PM

If you still have my project . . .

I'm getting an erroneous couple 400's in E3 and E4 once doing the macro by button click. Is there anyway to stop these from coming up without hiding them? Efficient and Clean Code for the Win! :-)

**gjlindn** · 08-08-2011, 01:48 PM

Sorry...which tab? Would this be easier if when you click the button it prompts you to highlight a range to apply the rounding to?

**BCossette** · 08-08-2011, 01:56 PM

Originally Posted by gjlindn

Sorry...which tab? Would this be easier if when you click the button it prompts you to highlight a range to apply the rounding to?

Hey,

Near Idle Tab, When you click Round RPM. Next Step will be to apply this same concept to Off Idle Sheet.

I don't think making a selection would help, it would take longer to complete it? I just want that entire column calculated regardless of the number of "Datalogs" (reading of sensors on engine, by row).

Also, can you look at http://www.excelforum.com/excel-programming/787304-filtering-data-keeping-rows-of-information-intact.html ?

I have attempted to code one via a regular macro (I'd have to remember to myself that I have a datalog limit, which would stink), and I tried the other one with VBA. The VBA was an example i found online and cant get it to suit my needs.

THANKS!!!!!!!!!!!!

**gjlindn** · 08-08-2011, 02:11 PM

Try updating your macro to this:

Please Login or Register  to view this content.

It has a couple of prompts, but if your data moves or is in different columns it will be easy to adjust this way.

As for your other post at http://www.excelforum.com/excel-prog...on-intact.html I have the same issue as BigBas...prehistoric version of Excel at work...so will have to wait until tonight. I'll lay off that one since BigBas is on it. Best of luck!

**BCossette** · 08-08-2011, 02:29 PM

Thanks gjlindn!

My last question, and i'm taking a break until BigBas can help me with that other issue tonight:

Same Sheet, Button "Simplify Near Idle". I have a basic macro there and it can stay that way as far as i'm concerned, but how do I Delete D E and F without Dragging down the entire column? Is there a way that it'll automatically just delete that entire column? I know there is a way, but I guess i'm just getting too complex for myself.

If i am not following the guidelines of this forum, you don't HAVE to do it for me, you can refer me to some references, but I won't complain if you guys just want to do the sections for me as far as ease for yourselves.

**gjlindn** · 08-08-2011, 02:59 PM

how do I Delete D E and F without Dragging down the entire column?

Deleting can mean at least two things to people. See these examples:

Please Login or Register  to view this content.

**gjlindn** · 08-08-2011, 03:06 PM

I'm thinking I should probably mention that if something seems like it should be simple, it very likely is. An easy way to learn simple VBA is to use the Excel Macro Recorder. It will usually create lots of extra, unnecessary lines of code, but it is pretty easy to clean up and will help you with the more simple Excel functions. Happy coding!

Round a Column of Numbers to a Specific List

LinkBack

Thread Tools

Rate This Thread

Display

Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Re: Round a Column of Numbers to a Specific List

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions