calculate percentage of cells that have any value

[JLeck]

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.

 

[StumpedAgain]

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

 

[daddylonglegs]

For range A1:A100 try

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

 

[T. Valko]

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

 

[Bob Phillips]

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

 

[JLeck]

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?

 

[RagDyer]

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.