Retrieving units and tens in Excel

Discussion in 'Math' started by atferrari, Feb 28, 2007.

1. atferrari Thread Starter AAC Fanatic!

Jan 6, 2004
2,663
784
Given a result of a previous calculation, (always positive and > 1000) I want Excel to show in another cell just the units and tens of that result.

Let's say I have 22768.

I need Excel to show just 68 (not as text but as a numeric value).

The value 22700 is unknow beforehand thus no substraction possible.

Any idea how to obtain that?

2. kubeek AAC Fanatic!

Sep 20, 2005
4,688
806
in programming language it would be n%100, which means the rest after division by 100. Don't know how it is in excel.

3. Papabravo Expert

Feb 24, 2006
10,340
1,850
The % notation in C is for the remainder after integer division. The MOD worksheet function is what you want:

Syntax
MOD(number,divisor)
Number is the number for which you want to find the remainder.
Divisor is the number by which you want to divide number.

Example: Cell A1 contains 22768
Cell B1 contains =MOD(A1,100) and the result is 68

4. atferrari Thread Starter AAC Fanatic!

Jan 6, 2004
2,663
784
Gracias PB!

RESIDUO is the Spanish name.

Feb 24, 2006
10,340
1,850

6. zgozvrm Member

Oct 24, 2009
115
2
Let's assume your value, 22768 resides in cell A1.

First, use the formula "=RIGHT(A1,2)" (enter this formula in cell A2.
This gives us "68" as text.

Then, use the formula "=VALUE(A2)" in cell A3 which converts the text value "68" to the numerical value 68.

Of course, you can combine these into one formula (let's put it in cell B1):
"=VALUE(RIGHT(A1,2))"

7. Papabravo Expert

Feb 24, 2006
10,340
1,850
Your method works as long as the cell contains an integer. The original problem was to isolate the tens and the units of a numeric quantity.

8. zgozvrm Member

Oct 24, 2009
115
2
An integer IS a numeric quantity!
I think you meant to say that my method works as long as the cell contains a string.

My method actually works for either (try it)! Although RIGHT is a string function, it works just as well with numerical values, but the result will be a string. That is why I surrounded it by the VALUE function.

This is just an alternative to the MOD function method you gave.

For floating point numbers (eg. 38726.48297), if you wanted to isolate the 1's and 10's (26), you could use:
=INT(MOD(A1,100))

9. Papabravo Expert

Feb 24, 2006
10,340
1,850
Yes of course you are correct about the RIGHT function. What I meant was the the numeric quantity 22768.75 gives different answers with the two methods. Not necessarily the intended answer in either case.

I think B1=INT(MOD(A1,100)) might fix that problem

10. zgozvrm Member

Oct 24, 2009
115
2
... which is what I said.

And, "Yes," it works (I tested it).

11. Papabravo Expert

Feb 24, 2006
10,340
1,850
And I agreed with you, no need to be defensive, but I did not actually go and test it. Age and experience do accord one some perks.

12. zgozvrm Member

Oct 24, 2009
115
2
Sorry, I wasn't being defensive... it just seemed as if you were stating something that I had already stated and weren't really sure if it worked.

I was simply stating that I had already said that, and that I had verified that it works.

No malice intended!