+ Reply to Thread
Results 1 to 11 of 11

IF formula with multiple Logic Tests and Varying TRUE results

  1. #1
    Registered User
    Join Date
    07-15-2014
    Location
    Wales
    MS-Off Ver
    2007
    Posts
    19

    IF formula with multiple Logic Tests and Varying TRUE results

    I am trying to create a basic sheet for tracking orders, fulfilment and balance chasing but I am struggling with what should be pretty simple IF formula.

    In the attached document I need cell O3 to give an answer if we need to chase the customer for the balance or not, it need to have multiple TRUE options also, this is the best way I can describe it:

    IF L3 = YES and J3 is more than £0 then the TRUE value with either be IF C3 is old than TODAY - 22 days then put "Call customer to Chase" but if the date in C3 is older then 50 days from TODAY then put "Send 1st Letter" but if C3 is older than 80 days from TODAY then put "Send 2nd Letter". All the FALSE values would be "No Action"

    I have tried a very basic IF that I understand in P3 but that is as far as my skills will let me go!

    Thanks in advance
    Andy

  2. #2
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2013
    Posts
    571

    Re: IF formula with multiple Logic Tests and Varying TRUE results

    Sorry, but I don't see any attachment?
    Click (*) if you received helpful response.

    Regards,
    David

  3. #3
    Registered User
    Join Date
    07-15-2014
    Location
    Wales
    MS-Off Ver
    2007
    Posts
    19

    Re: IF formula with multiple Logic Tests and Varying TRUE results

    With the attachment
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    07-15-2014
    Location
    Wales
    MS-Off Ver
    2007
    Posts
    19

    Re: IF formula with multiple Logic Tests and Varying TRUE results

    Should be there now, wasn't quick enough with my reply to add it on sorry!

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: IF formula with multiple Logic Tests and Varying TRUE results

    Try (untested)

    =IF(AND(L3="Yes",J3>0),IF(C3<TODAY()-80,"Send 2nd Letter",IF(C3<TODAY()-50,"Send 1st Letter",IF(C3<TODAY()-22,"Call customer to chase",""))),"")
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  6. #6
    Registered User
    Join Date
    07-15-2014
    Location
    Wales
    MS-Off Ver
    2007
    Posts
    19

    Re: IF formula with multiple Logic Tests and Varying TRUE results

    Looks great on quick testing thank you, as always I was not far off with the playing about I did to make it work but its the little things I always miss, an extra = or the wrong order etc.

    Thanks again,
    Andy

  7. #7
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: IF formula with multiple Logic Tests and Varying TRUE results

    =IF(AND(M3="Yes",K3>0),LOOKUP(C3,TODAY()-{42000,79,49,21},{"Send 2nd Letter","Send 1st Letter","Call customer to Chase","No Action"}),"No Action")
    Try this
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  8. #8
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: IF formula with multiple Logic Tests and Varying TRUE results

    or can try
    =IF(AND(M3="Yes",K3>0),INDEX({"No Action","Call customer to Chase","Send 1st Letter","Send 2nd Letter"},MATCH(C3,TODAY()-{0,22,50,80},-1)),"No Action")

  9. #9
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2013
    Posts
    571

    Re: IF formula with multiple Logic Tests and Varying TRUE results

    May be like this:
    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: IF formula with multiple Logic Tests and Varying TRUE results

    Quote Originally Posted by SDCh View Post
    May be like this:
    Please Login or Register  to view this content.
    That wont work, it will always default to the first condition
    IF(C3<=TODAY()-22
    ie
    IF C3 < TODAY() - 80 it is also less than TODAY() - 22 so the result will be "Chase customer" when it should be "send 2nd letter"
    Same goes for C3 < TODAY() - 50

    You need to reverse those three conditions so it's in lowest first order "TODAY() - 80", "TODAY() - 50", "TODAY() - 22"

  11. #11
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2013
    Posts
    571

    Re: IF formula with multiple Logic Tests and Varying TRUE results

    Ahh... Yes, you right Special-K, I'm forget that the formula c3 < Today()-22... not today()-c3 < 22 ....

    So it should be like this :
    Please Login or Register  to view this content.

+ 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. IF with multiple logical tests with text displayed if each true
    By danielball in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-04-2014, 09:32 PM
  2. Multiple logic tests in if(
    By Blackhawks in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-10-2014, 10:27 PM
  3. [SOLVED] If function Multiple logic tests each with its own outcome. How do I write it.
    By markDuffy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-16-2013, 04:29 PM
  4. [SOLVED] array formula count results of two tests
    By windsurferLA in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-25-2006, 07:35 PM
  5. [SOLVED] Countif Using Multiple Logic Tests
    By Carl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-04-2006, 02:15 AM

Tags for this Thread

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