## Put significance on tens and ones or round to a negative precision [updated!]

I had a situation in Excel where tens and ones are the only significant parts of a any number. For some Polish language-specific issues I have come with the situation that no matter how big number is, I need to know the value of its last two digits or last digit.

For example:

`21 --> 21 and 156 --> 56 and 6160 --> 60 and 01122 --> 22 and 2679014 --> 14 and 41053405 --> 5 and 5`

Learning the fact that `ROUND()`, `ROUNDUP()` and `ROUNDDOWN()` functions in Excel can be used with a negative precision parameter helped me solved this issue.

I am not too good in Excel, so maybe (update!) there are prettier solutions. This one works, however…

As per this source you can round a number to a negative precision to round given number to nearest:

• Thousands: A1 equals `321`; `=ROUND(A1,-3)` equals 1,000
• Hundreds: A1 equals `321`; `=ROUND(A1,-2)` equals 300
• Tens: A1 equals `321`; `=ROUND(A1,-1)` equals 320
• Ones: A1 equals `321`; `=ROUND(A1,0)` equals 321

Of course, you can “go below” to positive precision and get tenths, hundredths and thousandths respectively. But that was out of scope of my interest.

So, if I have 321 number and `=ROUND(A1,-1)` gives me 320 then if I subtract that rounding from that number, I’ll get 1 (an exact number at the ones position), which is what I want. Similarly, if I subtract `=ROUND(A1,-2)` from that number then I’ll get 21, which is again, what I need — an exact number at tens position.

Further tests revealed that this breaks when you have ones or tens number bigger than 50, but replacing `ROUND` with `ROUNDDOWN` fixed the problem. Ultimately I have ended up with:

• number at ones position: `B16-ROUNDDOWN(B16;-1)`
• number at tens position: `B16-ROUNDDOWN(B16;-2)`

Having these two I could start writing a formula for correctly pronouncing plural form of Polish numeral. Because Polish rules for getting them are far more complex than simple: add “s” at the end, if there are more than one! :)

## An update!

My friend told me to make use of RIGHT function instead. Good idea, only keep in mind that it works on strings not on numbers. 