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,645
    759
    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,670
    804
    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,136
    1,786
    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,645
    759
    Gracias PB!

    RESIDUO is the Spanish name.
     
  5. Papabravo

    Expert

    Feb 24, 2006
    10,136
    1,786
    De nada, senor
     
  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,136
    1,786
    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,136
    1,786
    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,136
    1,786
    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!
     
Loading...