Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formual Needed (Excel 2002 - XP)

    I need a formula that will place in a cell the whole number in increments of 8 (8, 16, or 24) and place the remainder in another cell.
    Cell C1 contains 4 (C1=4)
    Cell A2 contains 8 (A2=8)
    Cell B2 should contain the formula. It should add cells A2+C1 (8+4=12) then display the whole number 8.
    Cell C2 should display the remainder 4 (C2=4) or zero if the sum of A2+C1=8.

    If the total of A2+C1 is equal to or greater than 16, then the formula should display 16 and any remainder should be in cell C2 including zero. The totals of A2+C1 will never be greater than 24.

    Hope this makes sense and thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formual Needed (Excel 2002 - XP)

    In cell B2, enter the formula =INT((C1+A2)/8)
    In cell C2, enter the formula =MOD(C1+A2,8)

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formual Needed (Excel 2002 - XP)

    If I understanfd you, try the following in C2

    <big>=MOD(A2+C1,8)</big>

    and in B2 use

    <big>=A2+C1-C2</big>

    The MOD fucnction returns the remainder when a number is divided y a given number. For more detail check the Help.

    Andrew C

  4. #4
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formual Needed (Excel 2002 - XP)

    Hans,
    The formula =int((c1+a2)/8) results in 1.5. It should display 8.

    thanks

  5. #5
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formual Needed (Excel 2002 - XP)

    Andrew
    This works great. It was so simple, guess I could not figure it out (duh) and thanks also to Hans.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formual Needed (Excel 2002 - XP)

    =int((c1+a2)/8) can not equal 1.5, no matter WHAT a1 and c2 equal.

    it equals 1 if c1+a2 = 12.

    use
    =int((c1+a2)/8)*8 or the formulas of Andrew.

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formual Needed (Excel 2002 - XP)

    Andrew and Steve have already helped you, so this is just for completeness.

    My formula should have been =INT((C1+A2)/8)*8, as Steve pointed out. But if you got 1.5, you must have forgotten a pair of parentheses. The formula =INT(C1+A2)/8 would result in 1.5, but =INT((C1+A2)/8) results in 1 if C1=4 and A2=8.

  8. #8
    New Lounger
    Join Date
    Feb 2003
    Location
    Los Angeles, California, USA
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formual Needed (Excel 2002 - XP)

    Thanks again to you, Andrew, and Steve. I did get 1 and not 1.5 as I earlier indicated (duh).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •