Welcome Guest
You last visited August 16, 2017, 9:00 am
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
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)

United States
Member #41846
June 23, 2006
462 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.

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.

New Jersey
United States
Member #17843
June 28, 2005
64760 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!

Pennsylvania
United States
Member #2218
September 1, 2003
5573 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.

New Member
Hagerhill
United States
Member #166649
June 1, 2015
2 Posts
Offline
 Posted: June 2, 2015, 4:01 pm - IP Logged
 Thursday Friday Saturday Sunday Monday Tuesday Wednesday 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
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.

 Page 1 of 1