+ Reply to Thread
Results 1 to 9 of 9

If value is between range return value

  1. 08-01-2016, 04:25 AM #1
    nathanexcelhelp
    nathanexcelhelp is offline
    Registered User
    Join Date
    07-22-2016
    Location
    UK
    MS-Off Ver
    2007
    Posts
    57

    If value is between range return value

    I have a date of instruction in A2, a Todays date formula in B2 [B2 =TODAY()-A2] to calculate the date between the date in A2 to todays date.

    In C2 I wish it to return the following

    1. Up to 30 days '30% complete'
    2. Over 30 days – 60 days – '50% complete'
    3. Over 60 days to 90 days – '60% complete'
    4. Over 90 days to 180 days - '70% complete'
    5. Over 180 days – '80% complete'

    I have the first part sorted.

    =IF(B2 <30, "30% complete")

    Could someone help me add the rest of the date ranges to the above? Also the formula I have entered only calculates less than 30 days but I need it to be equal to 30 or less than 30.
    Reply With QuoteRegister To Reply
  2. 08-01-2016, 04:32 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,077

    Re: If value is between range return value

    Try this:

    =LOOKUP(TODAY()-A2,{30;60;90;180},{"30% complete";"60% complete";"70% complete";"80% complete"})
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 08-01-2016, 04:36 AM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,084

    Re: If value is between range return value

    Try this:

    =IF(B2<=30,"30% complete",IF(AND(B2>30,B2<=60),"50% Complete",IF(AND(B2>60,B2<=90),"60% Complete",IF(AND(B2>90,B2<=180),"70% Complete","80% Complete"))))
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh
    Reply With QuoteRegister To Reply
  4. 08-01-2016, 05:19 AM #4
    nathanexcelhelp
    nathanexcelhelp is offline
    Registered User
    Join Date
    07-22-2016
    Location
    UK
    MS-Off Ver
    2007
    Posts
    57

    Re: If value is between range return value

    Quote Originally Posted by Glenn Kennedy View Post
    Try this:

    =IF(B2<=30,"30% complete",IF(AND(B2>30,B2<=60),"50% Complete",IF(AND(B2>60,B2<=90),"60% Complete",IF(AND(B2>90,B2<=180),"70% Complete","80% Complete"))))
    Perfect Glenn Thank you very much!!!!
    Reply With QuoteRegister To Reply
  5. 08-01-2016, 05:20 AM #5
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    81,077

    Re: If value is between range return value

    My solution was shorter - did it not work for you???
    Reply With QuoteRegister To Reply
  6. 08-01-2016, 05:58 AM #6
    Feronen
    Feronen is offline
    Registered User
    Join Date
    07-26-2016
    Location
    Torun, Poland
    MS-Off Ver
    2007/2010/2013
    Posts
    23

    Re: If value is between range return value

    May I suggest another, different solution? It is IMO more elastic, and even more simple than @Glenn Kennedy's (with all respect to your expertise).
    First, you are creating a table, where you enter MINIMUM value of every range and certain text to this range.
    Then, you are using simple VLOOKUP, but with last parameter TRUE. Thanks to that last parameter, it will match proper text just by approximate value (less or equal to value that you are trying to find in a table).
    That means, you can use it easily for later if you'll have to change the ranges.

    Hope that this solution will help you


    My solution was shorter - did it not work for you???
    @AliGW I think your solution is great, but works onlny on certain values as 30,60 and so on. It doesn't work on another date difeerences, witch are also in the criteria range.
    Attached Files Attached Files
    Last edited by Feronen; 08-01-2016 at 06:04 AM.
    Reply With QuoteRegister To Reply
  7. 08-01-2016, 07:41 AM #7
    nathanexcelhelp
    nathanexcelhelp is offline
    Registered User
    Join Date
    07-22-2016
    Location
    UK
    MS-Off Ver
    2007
    Posts
    57

    Re: If value is between range return value

    Hi Feronen,

    Yes this would be another good way to do this. Just needed a slight adjustment to your data table where I needed to set the numbers to 0, 31, 61, 91, 181.

    Thank you
    Reply With QuoteRegister To Reply
  8. 08-01-2016, 09:19 AM #8
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: If value is between range return value

    Quote Originally Posted by Glenn Kennedy View Post
    Try this:

    =IF(B2<=30,"30% complete",IF(AND(B2>30,B2<=60),"50% Complete",IF(AND(B2>60,B2<=90),"60% Complete",IF(AND(B2>90,B2<=180),"70% Complete","80% Complete"))))
    If you start on the high end and work backwards you can eliminate the need for the AND functions.

    =IF(B2>180,...,IF(B2>=90,...,IF(B2>=60,...
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  9. 08-01-2016, 09:20 AM #9
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,084
    Quote Originally Posted by Tony Valko View Post
    If you start on the high end and work backwards you can eliminate the need for the AND functions.

    =IF(B2>180,...,IF(B2>=90,...,IF(B2>=60,...
    Yea... God but I am rusty at this game!!
    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. [SOLVED] return FROM TO values from custom defined range , and return all possible combination
    By raj soni in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2016, 01:38 PM
  2. [SOLVED] reference date time range, return the value into all cell that match datetime range
    By Jarvco13 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-22-2013, 10:39 PM
  3. formula to return blank value if cell va;ie not in range but vlookup if in range of cells
    By david1987 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-04-2013, 07:23 AM
  4. [SOLVED] Return value if within range, return multiple values if ranges overlap
    By cde1983 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-17-2013, 06:16 AM
  5. [SOLVED] Query Oracle using range in Excel to return individual records for each cell in range
    By bigwillydier in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-18-2013, 06:37 PM
  6. If cells values within a range match then return one value if not return another
    By Nicola13 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-12-2012, 06:26 AM
  7. If value appears in a range return 1 value, if not return another
    By whirlwind1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-21-2011, 11:23 AM

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