+ Reply to Thread
Results 1 to 5 of 5

Check if all values in a column are between two defined values

  1. #1
    Registered User
    Join Date
    06-02-2020
    Location
    Denmark
    MS-Off Ver
    10
    Posts
    6

    Check if all values in a column are between two defined values

    How do I make a function that checks if all numbers i a column are in between two defined values and returns true/falls?

    /Andreas

  2. #2
    Registered User
    Join Date
    02-01-2020
    Location
    UK
    MS-Off Ver
    Office2016
    Posts
    3

    Re: Check if all values in a column are between two defined values

    Try this, A1 is where i have the lower No and B1 the higher value the list of Nos are in Column C and formula in D1 dragged down, adjust according to your sheet.

    PHP Code: 
    =IF(AND(C1>=$A$1,C1<=$B$1),"True","False"
    GMW

  3. #3
    Registered User
    Join Date
    06-02-2020
    Location
    Denmark
    MS-Off Ver
    10
    Posts
    6

    Re: Check if all values in a column are between two defined values

    Thank you for your reply. This works, but it only checks one number (in your case "C1"). I get that i can just drag this to check all numbers if they are true or falls, but my criteria is that a lot of numbers that needs to be within a specific range, so I want to mark a full column of numbers and let excel tell me if all numbers are within that specified range. I hope that makes sense.

    AKJ

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,259

    Re: Check if all values in a column are between two defined values

    There are instructions at the top of the page explaining how to attach your sample workbook.
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,721

    Re: Check if all values in a column are between two defined values

    How about
    =COUNTIFS(E:E,">="&F1,E:E,"<="&G1)-COUNT(E:E)=0

    Where F1 has the lower limit & G1 the upper.

+ 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. [SOLVED] Count values of a defined range in a pivot table column
    By Hoareman in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 07-01-2020, 12:30 PM
  2. Find a value in a column based on a pre defined set of values
    By bsrivatsa in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-29-2019, 01:40 AM
  3. [SOLVED] lookup the intersection of a single column value and a column defined by 2 values
    By mosesthetank in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-15-2018, 01:38 PM
  4. Replies: 8
    Last Post: 09-29-2015, 12:53 PM
  5. Replies: 2
    Last Post: 01-07-2015, 09:06 AM
  6. [SOLVED] Create a defined number of copied cells for values found in a column
    By S.Taylor in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 11-08-2013, 04:25 AM
  7. Replies: 1
    Last Post: 03-15-2011, 11:16 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1