+ Reply to Thread
Results 1 to 5 of 5

A formula that returns "FALSE" if there is a different value in a range of identical cells

  1. #1
    Registered User
    Join Date
    12-03-2013
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    13

    A formula that returns "FALSE" if there is a different value in a range of identical cells

    Hello,
    I have a column/range (over 200,000) presumably of identical values.
    1. I would like to write a formula that returns the value "FALSE" if there is at least one cell containing a different value than the rest of the cells.
    2. I would like to write a formula that indicates how many values are different from the comparison parameter.
    example below:

    A1: 30
    A2: 30
    A3: 30
    A4: 31
    ....
    A151000: 30
    A151001:30
    A151002:28

    Formula 1: FALSE for the range A1:A151002
    Formula 2: 2 (the number of cells that contain a number that is not equal to "30")

    Thank you very much


    George

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: A formula that returns "FALSE" if there is a different value in a range of identical c

    Why don't you copy paste the data and apply remove duplicates and get each values count by way of using Countif() function?


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: A formula that returns "FALSE" if there is a different value in a range of identical c

    Or, try the following:

    Formula1:
    =IF(COUNTIF(A1:A151002,A1)=COUNTA(A1:A151002),"","FALSE")

    Formula2:
    =COUNTIFS(A1:A151002,"<>"&A1,A1:A151002,"<>"&"")

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: A formula that returns "FALSE" if there is a different value in a range of identical c

    Or

    User Pivot Table

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: A formula that returns "FALSE" if there is a different value in a range of identical c

    Perhaps
    =COUNTIF(A:A,"<>"&A1)
    in say C1, then simply =C1=0 to get the True/False.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

+ 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] Nested HLOOKUP returns "FALSE" in cell where result should be "0.00"
    By gammccubbin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2016, 09:49 AM
  2. "IF" formula to have "range of days" returns
    By benjamin_1986 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-15-2015, 10:29 AM
  3. [SOLVED] formula returns "false" on blank data
    By davemon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-18-2015, 03:07 PM
  4. [SOLVED] IF Statement Evaluates as "TRUE" but Returns "FALSE" Value (Excel 2007)
    By Simcik in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-11-2014, 12:43 PM
  5. [SOLVED] if formula returns ":false" instead of "0"
    By pja_14 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-20-2013, 04:09 AM
  6. Replies: 5
    Last Post: 10-12-2010, 06:46 AM
  7. Using "IF" with date returns an improper "FALSE"
    By scaffdog845 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-23-2008, 10:31 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