+ Reply to Thread
Results 1 to 6 of 6

IF AND formula help.

  1. #1
    Registered User
    Join Date
    07-23-2012
    Location
    GR, MI
    MS-Off Ver
    Excel 2007
    Posts
    3

    IF AND formula help.

    Hello,
    I am trying to write a formula to help me categorize date ranges. I have four categories, one for each quarter of the year. So, 1-90 days would be a "1", 91-180 would be a "2" and so on Template.xlsxup to 365 days as a "4". I started with an IF(AND formula but it is not producing the results I expected. "1"s are fine, but where I would expect it to return a "2" it instead produces "TRUE" and then "3" and "4" come back as "FALSE". Where did I go wrong and is this the most efficient way to do this? Thanks.

    =IF(D27<=90,1,IF(AND(D27>=91,D27<=180),2,)=IF(AND(D27>=181,D27>=270),3,)=IF((D27>=271),4,))

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,079

    Re: IF AND formula help.

    Try it like this:

    =IF(D27<=0,"",IF(D27<=90,1,IF(D27<=180,2,IF(D27<=270,3),IF(D27<=365,4,"")))))

    Check what happens in a leap year, though.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    07-23-2012
    Location
    GR, MI
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: IF AND formula help.

    I added that formula to my data but it says "You have entered too many arguments for this function"

  4. #4
    Registered User
    Join Date
    07-23-2012
    Location
    My Desk, Manchester, England
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: IF AND formula help.

    =if(a9<90,1,if(a9<180,2,if(a9<270,3,4)))

    Attempts to include 0 lead to True/False responses
    Last edited by DominicJ; 07-23-2012 at 11:08 AM.

  5. #5
    Registered User
    Join Date
    07-23-2012
    Location
    GR, MI
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: IF AND formula help.

    That one does the trick, Thanks to both of you

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,079

    Re: IF AND formula help.

    Sorry, I left a bracket in the middle. Try this:

    =IF(D27<=0,"",IF(D27<=90,1,IF(D27<=180,2,IF(D27<=270,3,IF(D27<=365,4,"")))))

    Hope this helps.

    Pete

+ 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