9 Replies Latest reply: Apr 16, 2014 7:12 AM by dvo247
tessrie Level 1 (0 points)

I am trying to write an IF/THEN statement in Numbers, and here's the problem:

 

Column N displays tons: <8 costs $215, 8 or greater costs $280.  I need to write an IF/THEN statement to give the monetary total per unit based on tonnage instead of trying to hand-jam it.

 

 

 

Tonnage

Impact Service Group Quarter 1 Cooling Season Start-up (April)

Impact Service Group Quarter 2 Cooling Season Inspection (July)

Impact Service Group Quarter 3 Heating Season Start-up (October)

Impact Service Group Quarter 4 Heating Inspection (January)

Totals per Store

15

$280 (more than 8)

 

 

 

 

15

$280


 

 

 

40

$280

 

 

 

 

5

$215 (less than 8)

 

 

 

 

 

Need this formula for work; project to turn in by COB tomorrow


MacBook Pro, iOS 5.1.1
  • Badunit Level 6 (11,615 points)

    =IF(N<8,215,280)

  • fan4bronco Level 1 (0 points)

    I have a similar question involving an even more complex if/then statement.  Depending upon the price entered in Column A, I want Column B to display a percentage of the value of A.  But the percentage isn't straightforward..it depends on the value of Column A.  In laymen's terms it would look like this:

     

    IF (Column A) is LESS than $50, THEN 15%(A)

    IF (A) is between 50.01-100, THEN 10%(A)

    IF (A) is GREATER than 100.01, THEN 5%(A)

     

    Is this possible? 

  • Jeff Shenk Level 4 (2,737 points)

    Try this:

     

    =IF(A<=50,0.15*A,IF(A<=100,0.1*A,0.05*A))

  • Barry Level 7 (29,949 points)

    You can also attack it from the opposite direction to that suggested by Jeff:

     

    IF(A>100,0.05*A,IF(A>50,0.1*A,0.15*A))

     

    Regards,

    Barry

  • fan4bronco Level 1 (0 points)

    This worked like a charm, and I was even able to add a few more IF/THEN statements to the formula.  Thanks so much for helping a Numbers novice.

  • dvo247 Level 1 (0 points)

    Hey if im trying to use multiple ranges for example if projections are less then 80,000 then 2, if greater then 80,000 then 3, if greater then 84,000 then 4. it works from below 80 to above 80 but once i go above 84 it keeps displaying 3. Any thoughts, thats my formula below? 

     

    IF(C1<80000,2,IF(C1≥80000,3,IF(C1>84000,4)

    Screen Shot 2014-04-15 at 10.05.48 PM.png

  • Barry Level 7 (29,949 points)

    Hi dvo,

     

    The formula is working correctly. Any value that is greater than 84000 will already have been tested and found greater than 80000. Since C1≥80000 it TRUE, the formula will return 3, and will not test C1>84000.

     

    Rearrange your formula:

     

    Existing: IF(C1<80000,2,IF(C1≥80000,3,IF(C1>84000,4)

     

    Revised: IF(C1>84000,4,IF(C1>=80000,3,2)

     

    Note that testing for C1<80000 is redundant. If C1 is not greater than 84000 and is not equal to or greater than 80000, then it must be less than 80000.

     

    Regards,

    Barry

  • Barry Level 7 (29,949 points)

    Hi fan,

     

    "I was even able to add a few more IF/THEN statements to the formula."

     

    Once you get beyond three nested IFs, you should be looking at a LOOKUP table and one of the LOOKUP functions, rather than a string of IFs.

     

    Regards,

    Barry

  • dvo247 Level 1 (0 points)

    Awesome thank you!!