+ Reply to Thread
Results 1 to 7 of 7

Formula to determine if a number fits in a range

  1. #1
    Registered User
    Join Date
    06-30-2014
    Location
    Denver, Colorado
    MS-Off Ver
    Microsoft 2013
    Posts
    35

    Formula to determine if a number fits in a range

    Hi. I'm trying to determine if a lab result fits within the target range. Ex: We want the patient's labs to be within 2.0-3.0 with her lab results being 2.5,2.3, 3.8, 1.7, 2.9 etc in column G4:G4567. I would like the formula to tell me if the lab result is low (lower than the desired range), high (higher than the desired range) or in range. Different formulas I've tried are:

    =IF($G4<2,"low",IF($G4>3,"high","in range"))

    =COUNTIF($G4,"<2.0")

    I tried a VLOOKUP with a table that looks like:

    1.9 Low
    2.0 In Range
    3.1 High

    =VLOOKUP(G4,$AE$3:$AF$5,2,TRUE)

    And nothing is working. "lows" are returned when it should be "high" or "in range" or I get an #NA or 0's.

    Is there a quick way to determine the lab value is in range or out of range (upper/lower)?

    Thanks!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to determine if a number fits in a range

    =IF($G4<2,"low",IF($G4>3,"high","in range"))

    That formula should work perfectly. Check to make sure that G4 is an actual value/number, and not text that looks like a number - test with =isnumber(G4) FALSE = text

    IF G4 is text and you cannot change it, try this...
    =IF($G4*1<2,"low",IF($G4*1>3,"high","in range"))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Formula to determine if a number fits in a range

    I was writing my reply when Ford responded with almost the same thing, it should work but must be reading it as text.
    your vlookup would work too if you change your low value to 0 instead of 1.9 but that too may be an issue with text vs number.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to determine if a number fits in a range

    Your 1st formula should work. If it's not then that tells me there's something wrong with your data.

    Are you sure the cell entries are true Excel numbers?

    If every cell contains a true Excel number, this formula should return 4564:

    =COUNT(G4:G4567)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    06-30-2014
    Location
    Denver, Colorado
    MS-Off Ver
    Microsoft 2013
    Posts
    35

    Re: Formula to determine if a number fits in a range

    Thank you everyone for replying! It turns out that G4 is a number, but the subsequent values in G are text when I do =isnumber(G5). Is it easier to convert the rest of the values to a number (if so, how would I do that?) or should I go ahead and use the other formula =IF($G4*1<2,"low",IF($G4*1>3,"high","in range")) ?

    Thanks again! You've saved me from having to look up each lab value manually

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula to determine if a number fits in a range

    Make sure the cells are not formatted as Text. Then select the column, Data > Text to columns, Finish.
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Registered User
    Join Date
    06-30-2014
    Location
    Denver, Colorado
    MS-Off Ver
    Microsoft 2013
    Posts
    35

    Re: Formula to determine if a number fits in a range

    Perfect! Thanks again!!

+ 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. Find smallest number that fits a criteria
    By HalPlz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-10-2014, 03:49 PM
  2. [SOLVED] Determine if a number is between a non-continuous range
    By Derek Cavanagh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-11-2013, 08:23 PM
  3. [SOLVED] Determine a Range despite number of rows
    By FCarv in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-23-2012, 11:10 AM
  4. How to determine number of Saturdays in a date range?
    By casper_1981 in forum Excel General
    Replies: 4
    Last Post: 03-09-2010, 02:53 PM
  5. [SOLVED] Formula to determine whether number falls within range??
    By Cat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-16-2005, 10:05 PM

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