Welcome Guest
Log In | Register )
You last visited December 3, 2016, 6:52 pm
All times shown are
Eastern Time (GMT-5:00)

Excel help...formula

Topic closed. 6 replies. Last post 2 years ago by SergeM.

Page 1 of 1
PrintE-mailLink
Avatar
New Member
Hagerhill
United States
Member #166649
June 1, 2015
2 Posts
Offline
Posted: June 1, 2015, 1:31 pm - IP Logged

I'm trying to create a formula that gives the result of the number of cells in a row that contain text. For example if I have four cells in a row and three contain text the result would be 75%, if I have seven cells in a row and four have text the result would be 57%.

This is what I have so far... =COUNTA(B9:H9)/COUNTA(B9:H9)

    Avatar

    United States
    Member #41846
    June 23, 2006
    458 Posts
    Offline
    Posted: June 2, 2015, 6:19 am - IP Logged

    counta looks for some text you specify.  It sounds like you just want to know if the cell is not empty, if so use countblank. this will return the number of blank cells in your specified range.

      SergeM's avatar - slow icon.png
      Economy class
      Belgium
      Member #123700
      February 27, 2012
      4035 Posts
      Offline
      Posted: June 2, 2015, 11:56 am - IP Logged

      Good question for general Excel-function use.

      The name of the function doesn't match the general understanding of the function-name.

      You just put slime fish in the boat.

        Raven62's avatar - binary
        New Jersey
        United States
        Member #17843
        June 28, 2005
        49642 Posts
        Offline
        Posted: June 2, 2015, 2:07 pm - IP Logged

        I'm trying to create a formula that gives the result of the number of cells in a row that contain text. For example if I have four cells in a row and three contain text the result would be 75%, if I have seven cells in a row and four have text the result would be 57%.

        This is what I have so far... =COUNTA(B9:H9)/COUNTA(B9:H9)

        (Number of Cells containing Text Divided by Total Number of Cells) Times 100 Equals Percent of Cells Contains Text.

        A mind once stretched by a new idea never returns to its original dimensions!

          winsumloosesum's avatar - Lottery-060.jpg
          Pennsylvania
          United States
          Member #2218
          September 1, 2003
          5387 Posts
          Online
          Posted: June 2, 2015, 2:48 pm - IP Logged

          I'm trying to create a formula that gives the result of the number of cells in a row that contain text. For example if I have four cells in a row and three contain text the result would be 75%, if I have seven cells in a row and four have text the result would be 57%.

          This is what I have so far... =COUNTA(B9:H9)/COUNTA(B9:H9)

          Not sure if this is what you need:

          https://app.box.com/s/t3ri3zgvrjr9pgxl7kcrrez66qibz65y

          I have a list of numbers and letters in cell range A1 through A100

          In cell C1 the formula is:

          =SUM(COUNTIF($A$1:$A$100,">0")/COUNTA($A$1:$A$100))

          Right click the cell C1, select the Number Tab, Select Percentage, I selected 1 decimal place.

            Avatar
            New Member
            Hagerhill
            United States
            Member #166649
            June 1, 2015
            2 Posts
            Offline
            Posted: June 2, 2015, 4:01 pm - IP Logged
            ThursdayFridaySaturdaySundayMondayTuesdayWednesday 
                           
            X  X        100.00%
            X            100.00%
                          #DIV/0!
            1. Row 3 has an X in the Thursday and Saturday Column. But if today is Saturday the should only be showing 66% compliant.
            2. Row 4 should only be showing 33% compliant...
            3. Row 5, I want it to read Non-Compliant
              SergeM's avatar - slow icon.png
              Economy class
              Belgium
              Member #123700
              February 27, 2012
              4035 Posts
              Offline
              Posted: June 2, 2015, 4:06 pm - IP Logged

              = countif( selection-range , "X")

              That is sufficient. You don't need it in percentages.