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.
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
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
- Hundreds: A1 equals
- Tens: A1 equals
- Ones: A1 equals
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
ROUNDDOWN fixed the problem. Ultimately I have ended up with:
- number at ones position:
- number at tens position:
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! :)
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.