Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
|
|||
|
|||
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:
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 Formula:
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate an average by a certain percentage? | Excel Worksheet Functions | |||
Calculate percentage based on cells with conditional formatting | New Users to Excel | |||
calculate percentage | Excel Worksheet Functions | |||
Forumla to calculate a percentage | Excel Discussion (Misc queries) | |||
calculate percentage of two cells | Excel Worksheet Functions |