calculate percentage of cells that have any value
 

I have a column of numbers, and I want to calculate how many rows have a
non-blank in them. Regardless of the value in the cells, I want to know what
percentage HAVE a (numeric) value. How do I do that? Thanks.

Answer: calculate percentage of cells that have any value
 

Here's how you can calculate the percentage of cells that have any value in a column:

1. First, select the column of numbers that you want to analyze.
2. Next, click on the Home tab in the Excel ribbon.
3. Then, click on the Find & Select button in the Editing group.
4. From the drop-down menu, select Go To Special.
5. In the Go To Special dialog box, select Constants and uncheck all other options.
6. Click OK to close the dialog box. This will select all cells in the column that have a value.
7. Now, look at the bottom right corner of the Excel window. You should see a status bar that shows the number of cells that are selected.
8. Divide this number by the total number of cells in the column to get the percentage of cells that have a value.

For example, if you have a column of 100 cells and 75 of them have a value, the percentage of cells with a value would be %.

calculate percentage of cells that have any value
 

Try the following. Replace "A1" for where your values start. Hope this helps!


Option Explicit
Sub percentnumeric()

Dim cell As Range
Dim i, j As Long

i = 0
j = 0

For Each cell In Range(Range("A1"), Range("A1").End(xlDown))
If IsNumeric(cell) And cell < "" Then i = i + 1
j = j + 1
Next cell

MsgBox (i & "/" & j & " have numeric values. This is " & i / j * 100 & "%.")

End Sub
--
-SA


"JLeck" wrote:

I have a column of numbers, and I want to calculate how many rows have a
non-blank in them. Regardless of the value in the cells, I want to know what
percentage HAVE a (numeric) value. How do I do that? Thanks.

calculate percentage of cells that have any value
 

For range A1:A100 try

=COUNT(A1:A100)/ROWS(A1:A100)

"JLeck" wrote:

I have a column of numbers, and I want to calculate how many rows have a
non-blank in them. Regardless of the value in the cells, I want to know what
percentage HAVE a (numeric) value. How do I do that? Thanks.

calculate percentage of cells that have any value
 

I want to know what percentage HAVE a (numeric) value.

Assume the range is A1:A10.

=COUNT(A1:A10)/ROWS(A1:A10)

Format as Percentage

--
Biff
Microsoft Excel MVP


"JLeck" wrote in message
...
I have a column of numbers, and I want to calculate how many rows have a
non-blank in them. Regardless of the value in the cells, I want to know
what
percentage HAVE a (numeric) value. How do I do that? Thanks.

calculate percentage of cells that have any value
 

=SUMPRODUCT(--(ISNUMBER(A1:A20)))/SUMPRODUCT(COUNTIF(A1:A20,{"","<"}))

--
__________________________________
HTH

Bob

"JLeck" wrote in message
...
I have a column of numbers, and I want to calculate how many rows have a
non-blank in them. Regardless of the value in the cells, I want to know
what
percentage HAVE a (numeric) value. How do I do that? Thanks.

calculate percentage of cells that have any value
 

Great! That looks good. What if I make it more complicated? Let's say I have
250 rows, but only the first 100 are populated. I want the formula to look at
only those rows that have been populated so far (but I want to keep my blank
rows to allow for growth). So I want to say something like:

What percentage of rows with a non-blank column A have a non-blank column B?

Can I do that?

"daddylonglegs" wrote:

For range A1:A100 try

=COUNT(A1:A100)/ROWS(A1:A100)

"JLeck" wrote:

I have a column of numbers, and I want to calculate how many rows have a
non-blank in them. Regardless of the value in the cells, I want to know what
percentage HAVE a (numeric) value. How do I do that? Thanks.

calculate percentage of cells that have any value
 

=COUNT(B1:B250)/COUNT(A1:A250)
--
David Biddulph

"JLeck" wrote in message
...
Great! That looks good. What if I make it more complicated? Let's say I
have
250 rows, but only the first 100 are populated. I want the formula to look
at
only those rows that have been populated so far (but I want to keep my
blank
rows to allow for growth). So I want to say something like:

What percentage of rows with a non-blank column A have a non-blank column
B?

Can I do that?

"daddylonglegs" wrote:

For range A1:A100 try

=COUNT(A1:A100)/ROWS(A1:A100)

"JLeck" wrote:

I have a column of numbers, and I want to calculate how many rows have
a
non-blank in them. Regardless of the value in the cells, I want to know
what
percentage HAVE a (numeric) value. How do I do that? Thanks.

calculate percentage of cells that have any value
 

If I follow you, Column A will be populated in order, while Column B may or
may not, and you want the percent of A that's filled, compared to what's
filled in the corresponding Column B.

If that sounds right, try this:

=COUNTA(B1:INDEX(B1:B250,MATCH(99^99,A1:A250)))/MATCH(99^99,A1:A250)

Format the cell containing the formula to percent.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JLeck" wrote in message
...
Great! That looks good. What if I make it more complicated? Let's say I
have
250 rows, but only the first 100 are populated. I want the formula to look
at
only those rows that have been populated so far (but I want to keep my
blank
rows to allow for growth). So I want to say something like:

What percentage of rows with a non-blank column A have a non-blank column
B?

Can I do that?

"daddylonglegs" wrote:

For range A1:A100 try

=COUNT(A1:A100)/ROWS(A1:A100)

"JLeck" wrote:

I have a column of numbers, and I want to calculate how many rows have
a
non-blank in them. Regardless of the value in the cells, I want to know
what
percentage HAVE a (numeric) value. How do I do that? Thanks.