+ Reply to Thread
Results 1 to 5 of 5

Nested IFS and multiple logical formulae

  1. #1
    Registered User
    Join Date
    08-28-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    9

    Post Nested IFS and multiple logical formulae

    Ok so this is a tricky one...

    I have uploaded a screenshot for reference- Cell J7 needs to the valid discount with three conditions. 1. there is a discount threshold meaning the consumer must purchase at least that quantity to get the discount, 2. "Tom Hanna" is excluded from all discounts, and 3. all T-shirts receive double the discount rate.

    I met the first two qualification with simple IF functions as you can see below and in the screen shot.

    =IF($A7="tom","0",IF($E7>=$I7,$G7*$H7,"0"))

    I can not figure out how to incorporate #3 into this SAME FUNCTION. Any thoughts guys?
    Attached Images Attached Images

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Nested IFS and multiple logical formulae

    try..
    =IF($A7="tom",0,IF(and(C7="T-shirt",$E7>=$I7),($G7*$H7)*2,$G7*$H7))

    if ever, you can post a real workbook instead on pictures.
    thanks.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Nested IFS and multiple logical formulae

    Please don't upload pictures. None of us are inclined to recreate your data when you have a workbook available - see guidelines in the Rules area of this forum.

    Try

    =IF($A7="tom","0",IF($E7>=$I7,$G7*$H7*IF(C7="T-Shirt",2,1),"0"))
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    08-28-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Nested IFS and multiple logical formulae

    vlady- yours worked for rows that had t shirts but did not correctly show a value of zero for those who did not meet the discount threshold

    I attached a sample excel so if you think you can help you can play around with it.
    Thanks guys!
    Attached Files Attached Files

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Nested IFS and multiple logical formulae

    jdohlinger,

    Attached is a modified version of your posted workbook.

    Because there could be a Tom Johnson that would get the discounts, I decided to check both the First and Last Name to make sure it is Tom Hanna. So, in J2 and copied down:
    Please Login or Register  to view this content.
    Then in K2 and copied down:
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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