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

adminMicrosoft0 Comment
3 min. reading

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 1
56 --> 56 and 6
160 --> 60 and 0
1122 --> 22 and 2
679014 --> 14 and 4
1053405 --> 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.

Leave a Reply