How to round off numbers in excel for given intervals

The following formula will do the trick:

=TRUNC(A1,-2)  +  XLOOKUP(TRUNC(A1,0)-TRUNC(A1,-2),$I$1:$I$7,$J$1:$J$7,,1)

Before starting to explain, I want to point out you have a conflict in the values to round to. At first, you have values down to 6 (so including 6, 7, and 8) rounding to 12, but at the end you want values below 9 (so including 6, 7, and 8) rounding to 00. My bet was you intended a "below 6" at the end and the rest of this assumes that. If <9 was correct, adjust the first entry in the lookup table that follows.

My original (deleted) answer kept getting changed with "Well, actually, there's an easier way to do that part so use this" and that got ridiculous after a bit. So here's the new, improved version of things.

You don't need (or want) to do any weird and complex actual rounding. So no worries about ROUND(), MROUND(), or INTEGER() and any of the oddities they bring with them. Nor the difficulties having strange rounding bands would bring.

You're going to take the input value, strip off the decimal part as it is not helpful in any way and is not desired in the final result. That will be done as an additional benefit of the fact that you are going to take apart the value and work with its parts.

First, you need the portion that is hundreds and higher. So for 10669, that means the 10600 part, dropping the final two digits off. That part is simple and never has anything done to it... it just waits around until the second part is figured out and gets added to it.

To do it, you use TRUNC() to truncate the value. The function's "Intellisense" help makes it seem like you enter how many digits from the original you want, but that's flat out wrong. You actually enter the number of decimal places you want kept. If you enter "0", you get an integer value that is just the original cut off at the decimal place. If you enter "2", you get two decimal places. If you enter "negative two: -2", you get the tens and ones digits cut off. It's a little misleading in that. You could think that means truncating 10669 to -2 digits would give you "106" (cutting those last two digits off), but what it really does is cut those off BUT replace them with zeros. So you get the "10600" you desire here.

Alrighty. So that's done and waiting for the final two digits to add to it. For that you need a two column, seven row table with the first column the breakpoints in your rounding rules and the second column the results for each one. You can copy and paste this one:

You could also load it into a Named Range so it doesn't have to appear ON your sheet, in case you'd prefer that or in case there's no room for it. To do that, actually enter the table on some sheet, let's say the I1:J7 I used, and use the following function, one NO ONE seems to know about for whatever reason:

=ARRAYTOTEXT(I1:J7,1)

Its output is: {6,0;15,12;30,20;50,40;70,60;90,80;99,0} (after you copy and Paset|Special|Values) which you can copy and paste into the Named Range's "Refers to" box. EASIEST way to load a table into anything, Named Range or otherwise, as it takes care of the commas and semi-colons for you.

(By the way, your questions makes it pretty clear you are not using an English version of Excel, or maybe more exactly, your Windows settings for things like decimal separators are different, so you will have to adjust anything I wrote to fit your situation.)

So, back to the second half of the formula. You use the table for a lookup. The lookup value is the final two digits of the truncated number. The easy way to get those two digits is to subtract the "truncated to -2 places" value from the complete "truncated to 0 places" number. So 10669-10600 gives you 69. That is looked up using XLOOKUP() not because I don't love VLOOKUP() but because it does something VLOOKUP() (my old, old friend) cannot: it lets you specify that you want an "Exact match or the next higher" which means that looking up "69" doesn't find an exact match, so it chooses the next higher value in the table, "70", and returns 60.

So now you have the two numbers the formula needs to add and it adds them giving you the apparently rounded result, but without ever "really" rounding.

Remember ARRAYTOTEXT(). It is hugely easier for creating array strings than loads of typing, making sure all the commas and semi-colons are right, or standard string techniques for building out long arrays that follow some kind of pattern or actually literally do the hard way what it does in one step. It's just amazing how many SE questions one can find in which people use convoluted methods to do its work! It's almost as bad as how many people use nasty, horrible looking formulas to find a column letter name when just using ADDRESS() with "1" for the row and the column number for the column, then SUBSTITUTE() to remove the extra bits.

Anyhow, that's it. No need for VBA. But if you know VBA, and are allowed to use it in your environment, it is always the gold standard for anything even halfway complicated.