+ Reply to Thread
Results 1 to 8 of 8

Creating a IF AND Formula

Hybrid View

ioswoody Creating a IF AND Formula 02-23-2018, 06:05 AM
finalazy Re: Creating a IF AND Formula 02-23-2018, 08:12 AM
ioswoody Re: Creating a IF AND Formula 02-23-2018, 07:00 PM
Sam Capricci Re: Creating a IF AND Formula 02-23-2018, 08:36 AM
Sam Capricci Re: Creating a IF AND Formula 02-23-2018, 07:28 PM
Sam Capricci Re: Creating a IF AND Formula 02-23-2018, 07:29 PM
ioswoody Re: Creating a IF AND Formula 02-23-2018, 08:06 PM
Sam Capricci Re: Creating a IF AND Formula 02-23-2018, 08:12 PM
  1. #1
    Registered User
    Join Date
    07-04-2012
    Location
    Sunshine Coast
    MS-Off Ver
    Excel for Mac 2011
    Posts
    83

    Question Creating a IF AND Formula

    I am trying to get the equation to process the formula based on the status in Column B

    For example Row 3 has sold 42 and their status is PT, so I want the bonus to be paid (which is B33 x F3) as the benchmark for PT is in B43 (40)
    Another example Row 8 has sold 5 and their status is CAS, so I don't want the bonus to be paid as the benchmark for CAS is in B44 (20)

    Sample File Attached and have highlighted the areas in YELLOW

    If you see any other areas of my spreadsheet that could be improved, feel free to let me know (I'm a novice)

    Thanks in advance!!
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    07-06-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    192

    Re: Creating a IF AND Formula

    Maybe something like this?
    =IF(AND(B3="CAS",F3>=$B$44),SUM($B$33*F3),IF(AND(OR(B3="PT",B3="FT"),F3>=$B$43),SUM($B$33*F3),0))

  3. #3
    Registered User
    Join Date
    07-04-2012
    Location
    Sunshine Coast
    MS-Off Ver
    Excel for Mac 2011
    Posts
    83

    Re: Creating a IF AND Formula

    That's awesome - Thanks!

    Now I have tried to add in the Any Team Member cell (so I can either do an incentive based on status or one where everyone needs to hit the benchmark), so I tried this but it doesn't work

    =IF(F2>=$B$42,SUM($B$33*F2),IF(AND(B2="FT",F2>=$B$43),SUM($B$33*F2),IF(AND(OR(B2="PT",B2="CAS"),F2>=$B$44),SUM($B$33*F2),0)))

    Hear from you soon

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: Creating a IF AND Formula

    Maybe this is what you want?
    =IF(AND(B2="PT",F2>=$B$44),$B$33*F2,IF(F2>=$B$42,$B$33*F2,"$0.00"))
    Old style writing formulas as SUM(B33*F3) are no longer used, you are simply taking B33*F3 so I replaced the sum parts since they are not needed.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: Creating a IF AND Formula

    here it is shortened...
    =IF(F2>=$B$42,$B$33*F2,IF(AND(B2="FT",F2>=$B$43),$B$33*F2,IF(AND(OR(B2="PT",B2="CAS"),F2>=$B$44),$B$33*F2,0)))
    it appears to work fine in your sample But I don't know what results you are looking for.
    and BTW, if I use the formula you have in post #4 yours also appears to return the same results and appears to work.

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: Creating a IF AND Formula

    One of the things recommended is when people post their sample sheet they also put in it the results they expect so contributors can evaluate the effectiveness of their formulas.

  7. #7
    Registered User
    Join Date
    07-04-2012
    Location
    Sunshine Coast
    MS-Off Ver
    Excel for Mac 2011
    Posts
    83

    Re: Creating a IF AND Formula

    Found out that I need to put a - in the cell that I am not using, the blank cell didn't work

    JOB COMPLETE!!
    Thank You All �� ����

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,684

    Re: Creating a IF AND Formula

    You’re welcome and thank you for the rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Use an existing cell formula for creating a new more complex formula
    By dubcap01 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-23-2014, 07:41 AM
  2. Replies: 1
    Last Post: 11-21-2012, 02:03 AM
  3. Need help creating a formula
    By garyarnzen@cinci.rr. in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-02-2008, 07:37 AM
  4. Help with creating formula
    By johnbb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-17-2008, 03:26 PM
  5. VBA:Creating Formula
    By dianahatesboys in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-13-2006, 06:00 PM
  6. help creating a formula using IF and AND
    By joe54345@gmail.com in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-16-2006, 05:25 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1