+ Reply to Thread
Results 1 to 4 of 4

Struggling with using IF and IF(AND) formulas

  1. 02-27-2014, 01:28 PM #1
    jbw1975
    jbw1975 is offline
    Registered User
    Join Date
    02-27-2014
    Location
    Kendal,England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Struggling with using IF and IF(AND) formulas

    Here's my problem. trying to create a formula within our commission sheet that will let my sales guys know what they will earn depending on what profit they earn.
    structure as follows.

    profit target say £5000,
    Achieve 0-79% of target get 7.5%
    Achieve 80-99% of target get 10%
    Achieve 100%+ of target get 12.5%

    i have managed to write the formula for the 1st and last using SUMIF but the middle formula is doing my head in. I am sure it is very simple but not for a simple chap like me.

    any help would be greatly appreciated.

    James.
    Reply With QuoteRegister To Reply
  2. 02-27-2014, 01:41 PM #2
    Moo the Dog
    Moo the Dog is offline
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Struggling with using IF and IF(AND) formulas

    Would something like this work:

    (A1) Profit Target ... (B1) 5000
    (A2) Actual Profit ... (B2) Enter actual profit in this cell
    (A3) Commission ... (B3) =B2*LOOKUP(B2/B1,{0,0.8,1},{0.075,0.1,0.125})

    - Moo
    Reply With QuoteRegister To Reply
  3. 02-27-2014, 02:31 PM #3
    russc2541
    russc2541 is offline
    Registered User
    Join Date
    01-06-2014
    Location
    Columbus, IN
    MS-Off Ver
    2013/O365
    Posts
    41

    Re: Struggling with using IF and IF(AND) formulas

    Or nest the IFs...longer text but works as well

    (A1) Profit Target ... (B1) 5000
    (A2) Actual Profit ... (B2) Enter actual profit in this cell
    (A3) Commission ... (B3) =IF(B2<0.8*B1,0.075*B2,IF(AND(B2>=0.8*B1,B2<B1),0.1*B2,IF(B2>B1,0.125*B2,"")))
    -Russell
    Reply With QuoteRegister To Reply
  4. 02-27-2014, 05:56 PM #4
    jbw1975
    jbw1975 is offline
    Registered User
    Join Date
    02-27-2014
    Location
    Kendal,England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Struggling with using IF and IF(AND) formulas

    Thank you very much, I can now have my life back

    cheers

    James
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Struggling with IF
    By KClark in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2011, 04:37 AM
  2. struggling
    By Tomas mcD in forum Excel General
    Replies: 1
    Last Post: 03-07-2011, 08:25 AM
  3. Struggling IF value
    By Rebecca in forum Excel General
    Replies: 5
    Last Post: 04-06-2006, 04:55 PM
  4. This should be easy, but I'm struggling
    By bobsmith58d in forum Excel General
    Replies: 1
    Last Post: 03-31-2005, 10:51 AM
  5. [SOLVED] Help, I'm Struggling!
    By Fybo in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-04-2005, 04:06 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1