Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency
I have a multi column spread sheet with numbers, text and dates in
separate columns. Some of the fields under each column have a blank in the column. I would like to find the frequency of the blank field in the column based on the date year in another column. Something like a: =CountIf(A2:A100,"=Blank") IF the corresponding(C2:C100,=2006) In other words, I only want to count the occurances in A2 IF the corresponding cell in C2 equals 2006. I want to find the frequency of the blanks based on the year to determine if users are entering data into a form field more often now or in the past. By the by, the tables that I am looking at, originate in Access. I am pulling them into Excel for this function. Thank you in advance for any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency
Assuming column C is formatted as DATE:
=SUMPRODUCT(--(A1:A20=" "),--(YEAR(C1:C20)=2006)) " wrote: I have a multi column spread sheet with numbers, text and dates in separate columns. Some of the fields under each column have a blank in the column. I would like to find the frequency of the blank field in the column based on the date year in another column. Something like a: =CountIf(A2:A100,"=Blank") IF the corresponding(C2:C100,=2006) In other words, I only want to count the occurances in A2 IF the corresponding cell in C2 equals 2006. I want to find the frequency of the blanks based on the year to determine if users are entering data into a form field more often now or in the past. By the by, the tables that I am looking at, originate in Access. I am pulling them into Excel for this function. Thank you in advance for any help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency
You can use a multi-step process to get the result.
Insert an extra column next to the one in which you want to count blank entries. Adapt the following if statement to match the columns for date and entry: For this statement data begins on row 2; dates are in column A; entries are in column B; the year =IF(B2="",YEAR(A2),"") Copy/fill the formula to the bottom of your data. The result will be a year entry on every row that has a blank entry. Then use the countif function to count the number of instences of each year in the range. " wrote: I have a multi column spread sheet with numbers, text and dates in separate columns. Some of the fields under each column have a blank in the column. I would like to find the frequency of the blank field in the column based on the date year in another column. Something like a: =CountIf(A2:A100,"=Blank") IF the corresponding(C2:C100,=2006) In other words, I only want to count the occurances in A2 IF the corresponding cell in C2 equals 2006. I want to find the frequency of the blanks based on the year to determine if users are entering data into a form field more often now or in the past. By the by, the tables that I am looking at, originate in Access. I am pulling them into Excel for this function. Thank you in advance for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
histograms - frequency and relative frequency? | Excel Discussion (Misc queries) | |||
3-D Frequency Chart in VBA | Charts and Charting in Excel | |||
Frequency formula | Excel Worksheet Functions | |||
Frequency for Histograms in Excel | Charts and Charting in Excel | |||
Cumulative Frequency | Charts and Charting in Excel |