determine round up/down amount

[alanlambden]

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

 

[tybaltlives]

well, here is what I came up with...

Excel 2010
	A	B
1	600302	1
2	600352	2
3	600411	2
4	600409	1
5	699990	1
Sheet1

Cell Formulas
Range	Formula
B1	=IF(100*ABS(RIGHT(A1,3)/100 -ROUND(RIGHT(A1,3)/100,0) )<=10,1,2)

 

[shg]

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​

 

[alanlambden]

Thanks Guys! That worked

 

[shg]

You're welcome.