Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Positives and Negatives (2003)

    Is there a true/false argument/formula that would tell me whether I have all positive values in a column, all negatives, and/or a mix. Also, is there a formula to count the number of columns with text only?
    Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Positives and Negatives (2003)

    Do you want to ignore blank cells? For example, if the range B1:B5 contains 3, 5, (blank), 2, 8 do you want to consider it as having all positives?

    What exactly do you mean by "the number of columns with text only"?

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Positives and Negatives (2003)

    Thanks Hans. Yes, I would like to ignore blank cells (i. e., if a cell is blank, treat it as if it were a positive). Regarding the text only request, in looking at a range of cells in a row, I would like to count the number of cells that contain only text, i. e., "John" or "Mary", as opposed to numerical data or blank.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Positives and Negatives (2003)

    The number of negative values in the range A1:H1 is =COUNTIF(A1:H1,"<0"). If this number is 0, there are no negative values, so you could use =COUNTIF(A1:H1,"<0")=0 to test for no negatives.
    Similarly, you can use =COUNTIF(A1:H1,">0")=0 to test for no positives.
    And you could use =AND(COUNTIF(A1:H1,"<0"),COUNTIF(A1:H1,">0")) to test for a mix of positive and negative.

    To count the number of text entries in A1:H1: =SUMPRODUCT(1*ISTEXT(A1:H1))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •