# determine round up/down amount

#### alanlambden

##### Board Regular
Hi Guys,

Ive got an array of numbers that i would like to categories by how much i need to round up or down to the nearest 100. If rounding to the nearest 100 is <= 10 then id like to index this number as 1. If >10, then index =2.
 600302 1 600352 2 600411 2 600409 1 699990 1

<tbody>
</tbody>

Hope this is clear, any help?

Al

Last edited:

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
well, here is what I came up with...

Excel 2010
AB
16003021
26003522
36004112
46004091
56999901
Sheet1
Cell Formulas
RangeFormula
B1=IF(100*ABS(RIGHT(A1,3)/100 -ROUND(RIGHT(A1,3)/100,0) )<=10,1,2)

Another way:

 Row\Col A​ B​ C​ 1​ 600302​ 1​ B1: =IF(ABS(MROUND(A1, 100) - A1) <=10, 1, 2) 2​ 600352​ 2​ 3​ 600411​ 2​ 4​ 600409​ 1​ 5​ 699990​ 1​

Thanks Guys! That worked

You're welcome.

Replies
3
Views
359
Replies
17
Views
1K
Replies
7
Views
315
Replies
2
Views
637
Replies
2
Views
428

1,196,119
Messages
6,013,570
Members
441,774
Latest member
esandoval

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back