Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: DISPLAY MATH RESULTS IN A DIFFERENT CELL

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Escondido, CA
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm currently using a spreadsheet that subtracts numbers and comes up with a positive or negative number as the results. Once the calculation is completed, I want the result to be displayed in a different cell. I have included 2 examples of my spreadsheet to better illustrate what I mean.


    In this example AUTH (61) minus ONHAND (63) = exce/short (2). I am in excess of my allowance by 2 and I want the excess number to be displayed in the EXCESS column and a 0 in the SHORT column. If the answer is 0, then I want a 0 in both the EXCESS and the SHORT cells.

    1 BIN # AUTH ONHAND exce/short EXCESS SHORT
    2 09517 61 63 2 2 0
    3 32197 4 4 0 0 0



    In this example AUTH (198) minus ONHAND (177) = exce/short (-21). I am short of my allowance by 21 and I want the exce/short number to be displayed in the SHORT column and a 0 in the EXCESS column.

    BIN # AUTH ONHAND exce/short EXCESS SHORT
    27 44312 198 177 -21 0 -21

    In both examples I have to manually type the exce/short number in the appropriate EXCESS and SHORT columns, and I have over 200 rows of data I have to manually type the result in. Is there a way for excel to automatically display the data in the appropriate column and a 0 in the other column?

    Any assistance you can provide will be greatly appreciated.

    Thanks,

    Gary Edgar

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Sydney/Brisbane , Australia
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    say the exce/short=col3
    excess=col4
    short=col5

    now a4=if(a3<0,a3,0)
    and a5=if(a3>0,a3,0)

    does this do what you want?

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If your entries laid out as
    A1 ... BinNumber
    B1 ... AUTH
    C1 ... ONHAND
    D1 ... exc/short
    E1 ... excess
    F1 ... short

    then formula in D2 ... =C2-B2
    then formula in E2 ... =IF($D2>0,$D2,0)
    then formula in F2 ... =IF($D2<0,$D2,0)

    Hope This Helps!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-08 20:33, Yogi Anand wrote:
    If your entries laid out as
    A1 ... BinNumber
    B1 ... AUTH
    C1 ... ONHAND
    D1 ... exc/short
    E1 ... excess
    F1 ... short

    then formula in D2 ... =C2-B2
    then formula in E2 ... =IF($D2>0,$D2,0)
    then formula in F2 ... =IF($D2<0,$D2,0)

    Hope This Helps!
    With Yogi's setup, you can also try the following:

    E2: =MAX(C2-B2,0)
    F2: =MIN(C2-B2,0)

    Frees up one column, too.

    Bye,
    Jay

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Jay!
    Your suggestion also avoids using the IF function ... this way we will save that big gun for bigger game.

    Regards!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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