+ Reply to Thread
Results 1 to 7 of 7

Problem with Multiple IF AND Function

  1. 11-14-2019, 05:35 AM #1
    chandramouliarun
    chandramouliarun is offline
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Red face Problem with Multiple IF AND Function

    Hi All,

    Hope you are doing well!...I have a problem with the following multiple IF-AND function...Can you please help here....

    =IF(G2<=5,"1-5",if(AND(G2>5,G2<=10),"6-10",IF(AND(G2>10,G2<=20),"11-20",IF(AND(G2>20,G2<=30),"21-30",IF(AND(G2>30,G2<=50),"31-50",IF(AND(G2>50,G2<=100),"51-100",IF(AND(G2>100,G2<=150),"101-150",IF(AND(G2>150,G2<=200),"151-200",IF(AND(G2>200,G2<=250),"201-250",IF(G2>250),"Greater than 250"))))))))


    I get an error dialogbox saying that "There's a problem with this formula ..Not trying to resolve a formula"


    Can you please help here...

    Thanks,
    Arun
    Reply With QuoteRegister To Reply
  2. 11-14-2019, 05:40 AM #2
    The_Snook
    The_Snook is offline
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2021 H: 365
    Posts
    940

    Re: Problem with Multiple IF AND Function

    Hi chandramouliarun,

    Try this:

    =IF(G2<=5,"1-5",if(AND(G2>5,G2<=10),"6-10",IF(AND(G2>10,G2<=20),"11-20",IF(AND(G2>20,G2<=30),"21-30",IF(AND(G2>30,G2<=50),"31-50",IF(AND(G2>50,G2<=100),"51-100",IF(AND(G2>100,G2<=150),"101-150",IF(AND(G2>150,G2<=200),"151-200",IF(AND(G2>200,G2<=250),"201-250",IF(G2>250,"Greater than 250")))))))))

    Regards,

    Snook
    Reply With QuoteRegister To Reply
  3. 11-14-2019, 05:47 AM #3
    davsth
    davsth is offline
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,212

    Re: Problem with Multiple IF AND Function

    Firstly the ifs are dependant on the previous if so
    IF(G2<=5,"1-5",if(G2<=10,"6-10",IF(G2<=20,"11-20",IF(G2<=30,"21-30",IF(G2<=50,"31-50",IF(G2<=100,"51-100",IF(G2<=150,"101-150",IF(G2<=200,"151-200",IF(G2<=250,"201-250",IF(G2>250,"Greater than 250")))))))))

    so a lot shorter but shorter still with a vlookup
    https://www.ablebits.com/office-addi...-if-statement/
    Reply With QuoteRegister To Reply
  4. 11-14-2019, 05:57 AM #4
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,619

    Re: Problem with Multiple IF AND Function

    First: seems you really have problems with closing parthenses
    yjis one IF(G2>250) is not neede
    then at ,"Greater than 250")))))))))the end there shall be two more. So:
    ,"Greater than 250"))))))))))

    The second issue - if each of your cases have a direct neighbour (no situation "11-20" then nothing then "31-50") you do not have to use AND at all. And you can skip the last condition - if G2 is NOT <+ 250 then it's for sure greater than 250

    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Third: it could be done also with VLOOKUP function would be much shorter
    Best Regards,

    Kaper
    Reply With QuoteRegister To Reply
  5. 11-14-2019, 06:02 AM #5
    chandramouliarun
    chandramouliarun is offline
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Re: Problem with Multiple IF AND Function

    Thanks Snook!...Appreciate your help!


    Thanks,
    Arun
    Reply With QuoteRegister To Reply
  6. 11-14-2019, 06:03 AM #6
    chandramouliarun
    chandramouliarun is offline
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Re: Problem with Multiple IF AND Function

    Thanks Kaper!...
    Reply With QuoteRegister To Reply
  7. 11-14-2019, 06:03 AM #7
    chandramouliarun
    chandramouliarun is offline
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Re: Problem with Multiple IF AND Function

    Thanks Davsth!...
    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. [VBA] Problem with outmail function - function sends mails only to 1 recepient
    By sauron12 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-21-2015, 03:30 PM
  2. Excel Problem with worksheet, need to know which function for penny a day doubled problem
    By excellhobo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-20-2013, 07:16 PM
  3. [SOLVED] VBA Function to calculate totals - Problem with multiple workbooks
    By Hood in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-28-2013, 02:31 PM
  4. Problem Finding Data in Multiple Columns Using Match Function
    By joshc22 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-12-2012, 04:26 PM
  5. [SOLVED] Multiple IF function problem
    By drakaz in forum Excel General
    Replies: 6
    Last Post: 04-17-2012, 05:28 AM
  6. Nested if and Vlookup function problem - multiple worksheets
    By Mariesmi80 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-11-2008, 03:39 PM
  7. IF function with multiple results: No problem for you guys, but for me...
    By azores in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-01-2005, 01:10 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1