IF Statement help please - again - got it wrong last time!

Discussion in 'Microsoft Excel Worksheet Functions' started by Alan Davies, May 11, 2006.

  1. Alan Davies

    Alan Davies Guest

    i run a spreadsheet that calculates commision for sales people
    Column I is a product list - 40 products or so
    Column P is total sales value
    Column Z is the name of a sales person who shares the commission with the
    main "owner" of the worksheet. (Each worksheet is for one sales person -but
    commission can be shared), there are 6 potenial sharers all of whom are in a
    list called TEAM
    One product"PLAN" shares the commission 50-50 between the sheet owner and
    the person in Column Z, all other products share 70-30

    How do i write an IF statement that says something like:
    IF(I3="PLAN" and Z3="Mark or Kath or Steve etc", p3*.5, but if I3="Anything
    apart from PLAN" and Z3="mark or kath or steveetc",P3*.7)
     
    Alan Davies, May 11, 2006
    #1
    1. Advertisements

  2. Alan Davies

    Biff Guest

    Hi!

    Maybe something like this:

    =IF(AND(I3="PLAN",OR(Z3={"Mark","Kath","Steve"})),P3*0.5,IF(AND(I3<>"PLAN",OR(Z3={"Mark","Kath","Steve"})),P3*0.7,""))

    Biff

    "Alan Davies" <> wrote in message
    news:...
    >
    > i run a spreadsheet that calculates commision for sales people
    > Column I is a product list - 40 products or so
    > Column P is total sales value
    > Column Z is the name of a sales person who shares the commission with the
    > main "owner" of the worksheet. (Each worksheet is for one sales
    > person -but
    > commission can be shared), there are 6 potenial sharers all of whom are in
    > a
    > list called TEAM
    > One product"PLAN" shares the commission 50-50 between the sheet owner and
    > the person in Column Z, all other products share 70-30
    >
    > How do i write an IF statement that says something like:
    > IF(I3="PLAN" and Z3="Mark or Kath or Steve etc", p3*.5, but if
    > I3="Anything
    > apart from PLAN" and Z3="mark or kath or steveetc",P3*.7)
    >
    >
     
    Biff, May 11, 2006
    #2
    1. Advertisements

  3. Alan Davies

    Bob Phillips Guest

    =IF(AND(I3="Business",OR(Z3={"Mark","Steve","Kath","Doug"})), P3*0.5,
    IF(AND(I3="Person",OR(Z3={"Mark","Steve","Kath","Doug"})),P3* 0.7,""))

    --
    HTH

    Bob Phillips

    (remove xxx from email address if mailing direct)

    "Alan Davies" <> wrote in message
    news:...
    >
    > i run a spreadsheet that calculates commision for sales people
    > Column I is a product list - 40 products or so
    > Column P is total sales value
    > Column Z is the name of a sales person who shares the commission with the
    > main "owner" of the worksheet. (Each worksheet is for one sales

    person -but
    > commission can be shared), there are 6 potenial sharers all of whom are in

    a
    > list called TEAM
    > One product"PLAN" shares the commission 50-50 between the sheet owner and
    > the person in Column Z, all other products share 70-30
    >
    > How do i write an IF statement that says something like:
    > IF(I3="PLAN" and Z3="Mark or Kath or Steve etc", p3*.5, but if

    I3="Anything
    > apart from PLAN" and Z3="mark or kath or steveetc",P3*.7)
    >
    >
     
    Bob Phillips, May 12, 2006
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. stan

    Try Again: IF Statement w/ Nested ANDand OR

    stan, Nov 17, 2003, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    190
    darkangelstorm
    Nov 18, 2003
  2. RichardO

    min and if statement again

    RichardO, Apr 8, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    228
    Frank Kabel
    Apr 8, 2004
  3. Guest

    Help please, IF statement/SUMIF statement

    Guest, Jan 7, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    23
    Views:
    469
    JulieD
    Jan 11, 2005
  4. prpball

    excel is calculating wrong again.

    prpball, Jan 26, 2010, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    233
    Bill Sharpe
    Feb 1, 2010
  5. prpball

    excel is calculating wrong again

    prpball, Jan 26, 2010, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    298
    Chip Pearson
    Jan 26, 2010
Loading...

Share This Page