+ Reply to Thread
Results 1 to 11 of 11

I Need Help Creating a Formula to That Sorts True/False Results

  1. #1
    Forum Contributor
    Join Date
    10-05-2010
    Location
    CA
    MS-Off Ver
    Excel 365
    Posts
    539

    Question I Need Help Creating a Formula to That Sorts True/False Results

    Hello,
    I need help creating a formula that scans one column searching for cells that contain the letter X, Y, and Z. Then finds columns where X or Y is false but Z is true. The final result would be the column heading of the first column that was different.

    Example:
    Column "J" contains X's, Y's, and Z's and columns “L – T” contain True or False results and have headings numbered 1 through 9. The formula would check if there was an X or Y in its row in the cell for column “J” and if so it would look for instances in columns “L – T” were either X or Y were false but rows containing a Z were true. The heading of the first instance this occurred of said column would be the result.

    A spreadsheet with an example is attached for a better understanding of what I'm trying to accomplish.

    Thank you in advance for any and all help.
    Attached Files Attached Files
    Last edited by artiststevens; 09-09-2013 at 02:25 AM.

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: I Need Help Creating a Formula to That Sorts True/False Results

    =IF(OR(J2={"X","Y"}),MATCH(TRUE,$L2:$T2<>$L$14:$T$14,0),IF(J2="Z","Z","None"))
    Confirm Contrl+Shift+enter
    Drag down
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Forum Contributor
    Join Date
    10-05-2010
    Location
    CA
    MS-Off Ver
    Excel 365
    Posts
    539

    Question Re: I Need Help Creating a Formula to That Sorts True/False Results

    Thank you Robert I appreciate the help. The formula works sometimes and other times it doesn't. I've attached another spreadsheet with more data and further explanation. Thank you for any and all help.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-06-2013
    Location
    Winterville, NC, USA
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: I Need Help Creating a Formula to That Sorts True/False Results

    The formula doesn't work because you don't have any text. In cell W3 it says "none" thats because J3 doesn't have an "x", "y", nor a "z" so it doesn't return a value. Cell Y2 are you wanting that cell to count all "True"s?

  5. #5
    Forum Contributor
    Join Date
    10-05-2010
    Location
    CA
    MS-Off Ver
    Excel 365
    Posts
    539

    Re: I Need Help Creating a Formula to That Sorts True/False Results

    The Xs, Ys, and Zs are not supposed to be in every cell. I only want the true's counted that have an X in column J.

  6. #6
    Registered User
    Join Date
    09-06-2013
    Location
    Winterville, NC, USA
    MS-Off Ver
    Excel 2013
    Posts
    23

    Re: I Need Help Creating a Formula to That Sorts True/False Results

    try this formula =IF(AND(COUNTIF(L2:V2,TRUE),(J2="x"))=TRUE,COUNTIF(L2:V2,TRUE),"")
    this will count the number of trues IF "x" is in the J column but it wont count if y or z is there

    hope that helps
    Last edited by slasherdan; 09-07-2013 at 01:51 AM.

  7. #7
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: I Need Help Creating a Formula to That Sorts True/False Results

    Quote Originally Posted by artiststevens View Post
    Thank you Robert I appreciate the help. The formula works sometimes and other times it doesn't. I've attached another spreadsheet with more data and further explanation. Thank you for any and all help.
    What do the yellow rows indicate?
    Can you state the expected result for each row?
    Are we looking at first "Z",last,closest to any X,Y?

  8. #8
    Forum Contributor
    Join Date
    10-05-2010
    Location
    CA
    MS-Off Ver
    Excel 365
    Posts
    539

    Arrow Re: I Need Help Creating a Formula to That Sorts True/False Results

    Quote Originally Posted by RobertMika View Post
    What do the yellow rows indicate?
    Can you state the expected result for each row?
    Are we looking at first "Z",last,closest to any X,Y?
    Thank you for getting back to be Robert. I answered your questions below and attached an expanded spreadsheet that breaks what I'm looking for even further.

    What do the yellow rows indicate?
    They indicate the close of a 2 hour cycle. They're no important to what I'm trying to accomplish.

    Can you state the expected result for each row?
    I broke down each X, Y, and Z column by column. I hope it makes it clearer what I need.

    Are we looking at first "Z",last,closest to any X,Y?
    I'm looking for all "Z"s. I want every "Z" to be True in a column and any X or Y False.


    Thank you again for any and all help.
    Attached Files Attached Files

  9. #9
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: I Need Help Creating a Formula to That Sorts True/False Results

    sort_help_expanded1.xlsx
    With heleer columns.

  10. #10
    Forum Contributor
    Join Date
    10-05-2010
    Location
    CA
    MS-Off Ver
    Excel 365
    Posts
    539

    Re: I Need Help Creating a Formula to That Sorts True/False Results

    Thank you very much Robert for all of your help. I greatly appreciate you creating a formula that does exactly what I need it to do. I've have given you rep and once again I appreciate the time and energy to put into making this work. Thank you.

  11. #11
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: I Need Help Creating a Formula to That Sorts True/False Results

    You are welcome.

+ 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. Replies: 0
    Last Post: 04-22-2013, 01:44 PM
  2. Replies: 14
    Last Post: 06-27-2012, 04:26 PM
  3. Formula that will give me True or False results
    By Reenee in forum Excel General
    Replies: 2
    Last Post: 02-02-2009, 10:36 AM
  4. [SOLVED] My If statement results in False when actually true
    By papa jonah in forum Excel General
    Replies: 7
    Last Post: 03-17-2005, 08:06 PM
  5. [SOLVED] Adding True False Results
    By Arla M in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-27-2005, 03:06 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