Numbers help with "IF" stmt and "then"

Discussion in 'Mac Apps and Mac App Store' started by bizcoach, Nov 24, 2009.

  1. bizcoach macrumors newbie

    Joined:
    Nov 24, 2009
    #1
    New to the posts... familiar with Xcel IF/THEN stmts and trying to create the following in Numbers:
    A sales commission spreadsheet whereas if accumulated commission calculated is <x, then commission=n. If commission calculated is =>x but <y, then commission=n. If commission is =>y, the commission=n.
    In other words, statements that will calculate a percentage based on <=> conditions. I'm having difficulty interpreting the user guide for these commands. Thanks!
     
  2. mysterytramp macrumors 65816

    mysterytramp

    Joined:
    Jul 17, 2008
    Location:
    Maryland
    #2
    You're a bit confusing here. A, your commission is always n; B, you have accumulated commission and commission; and C, Excel and Numbers use the same IF() statements.

    I'm going to assume what you say as "accumulated commission" is sales and commission is a percentage of those sales (and if I'm wrong, you should see how to get what you want. I'm also going to assume that you've got three different commissions.

    One last assumption: Sales is contained in cell B2

    Work from the simplest and work your way to a more complex formula.

    So, I'll start with the last:

    =if(B2=>100000,50000,0)

    That is, if sales exceed 100000, the commission is 50000 and if not, they're 0.

    You can replace the 0 with another if statement

    =IF(B2>=100000,50000,IF(B2<=75000,10000,25000))

    That should do it.

    If sales are greater than or equal to 100000, the commission is 50000

    If sales are less than or equal to 75000, the commission is 10000

    All other commissions are 25000

    You can replace the commissions with calculations:

    =IF(B2>=100000,B2*.5,IF(B2<=75000,B2*.1,B2*.25))

    This is the "simplest" way to do a commission table. If things get more complicated, you'll probably want to use the LOOKUP functions.

    Hope this helps

    mt
     

Share This Page