| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Percentage of the occurance of a word in a date range
I have a spreadsheet with dates in Column A, and either the word "Res" or
"Comm" in Column M. How can I find out the percentage of "Res" for all the records in January? PS - there is an abundance of extreme talent in this community! Thanks for all your postings! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Percentage of the occurance of a word in a date range
Assuming there are no empty cells in the date range (empty cells will
evaluate as month January). =SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<"")) Format as PERCENTAGE -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... I have a spreadsheet with dates in Column A, and either the word "Res" or "Comm" in Column M. How can I find out the percentage of "Res" for all the records in January? PS - there is an abundance of extreme talent in this community! Thanks for all your postings! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Percentage of the occurance of a word in a date range
Valko,
Thanks for the reply. I'm still having trouble (probably because I don't have a good grasp of arrays). I tweaked it a little because I am referencing a different worksheet named "Life Cycle". My data starts at row 10 and I need to caluclate all future entries, so I adjusted the range. However my data currently only has 150 rows. Any additional tips? Jeez, I'm dumb! =SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900="Res"))/SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900<"")) "T. Valko" wrote: Assuming there are no empty cells in the date range (empty cells will evaluate as month January). =SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<"")) Format as PERCENTAGE -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... I have a spreadsheet with dates in Column A, and either the word "Res" or "Comm" in Column M. How can I find out the percentage of "Res" for all the records in January? PS - there is an abundance of extreme talent in this community! Thanks for all your postings! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Percentage of the occurance of a word in a date range
Valko,
I was right in my last post - I'm dumb. I found one invalid entry. User error, your function worked great! Thanks so much! "Art-SNL" wrote: Valko, Thanks for the reply. I'm still having trouble (probably because I don't have a good grasp of arrays). I tweaked it a little because I am referencing a different worksheet named "Life Cycle". My data starts at row 10 and I need to caluclate all future entries, so I adjusted the range. However my data currently only has 150 rows. Any additional tips? Jeez, I'm dumb! =SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900="Res"))/SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900<"")) "T. Valko" wrote: Assuming there are no empty cells in the date range (empty cells will evaluate as month January). =SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<"")) Format as PERCENTAGE -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... I have a spreadsheet with dates in Column A, and either the word "Res" or "Comm" in Column M. How can I find out the percentage of "Res" for all the records in January? PS - there is an abundance of extreme talent in this community! Thanks for all your postings! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Percentage of the occurance of a word in a date range
I'm still having trouble
The formula looks OK, what result do you get? -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... Valko, Thanks for the reply. I'm still having trouble (probably because I don't have a good grasp of arrays). I tweaked it a little because I am referencing a different worksheet named "Life Cycle". My data starts at row 10 and I need to caluclate all future entries, so I adjusted the range. However my data currently only has 150 rows. Any additional tips? Jeez, I'm dumb! =SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900="Res"))/SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900<"")) "T. Valko" wrote: Assuming there are no empty cells in the date range (empty cells will evaluate as month January). =SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<"")) Format as PERCENTAGE -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... I have a spreadsheet with dates in Column A, and either the word "Res" or "Comm" in Column M. How can I find out the percentage of "Res" for all the records in January? PS - there is an abundance of extreme talent in this community! Thanks for all your postings! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Percentage of the occurance of a word in a date range
The formula is great. I forgot to mention that some of the data is from last
year. How can I tweak this formula to only show the "Res" for January of 2008 (excluding 2007)? Thanks, Art "T. Valko" wrote: I'm still having trouble The formula looks OK, what result do you get? -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... Valko, Thanks for the reply. I'm still having trouble (probably because I don't have a good grasp of arrays). I tweaked it a little because I am referencing a different worksheet named "Life Cycle". My data starts at row 10 and I need to caluclate all future entries, so I adjusted the range. However my data currently only has 150 rows. Any additional tips? Jeez, I'm dumb! =SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900="Res"))/SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900<"")) "T. Valko" wrote: Assuming there are no empty cells in the date range (empty cells will evaluate as month January). =SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<"")) Format as PERCENTAGE -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... I have a spreadsheet with dates in Column A, and either the word "Res" or "Comm" in Column M. How can I find out the percentage of "Res" for all the records in January? PS - there is an abundance of extreme talent in this community! Thanks for all your postings! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Percentage of the occurance of a word in a date range
Add an array to *each* SUMPRODUCT function like this:
--(YEAR('Life Cycle'!A10:A900)=2008) Since you're testing for a specific year you don't have to be concerned about empty cells evaluating as month January. -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... The formula is great. I forgot to mention that some of the data is from last year. How can I tweak this formula to only show the "Res" for January of 2008 (excluding 2007)? Thanks, Art "T. Valko" wrote: I'm still having trouble The formula looks OK, what result do you get? -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... Valko, Thanks for the reply. I'm still having trouble (probably because I don't have a good grasp of arrays). I tweaked it a little because I am referencing a different worksheet named "Life Cycle". My data starts at row 10 and I need to caluclate all future entries, so I adjusted the range. However my data currently only has 150 rows. Any additional tips? Jeez, I'm dumb! =SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900="Res"))/SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900<"")) "T. Valko" wrote: Assuming there are no empty cells in the date range (empty cells will evaluate as month January). =SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<"")) Format as PERCENTAGE -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... I have a spreadsheet with dates in Column A, and either the word "Res" or "Comm" in Column M. How can I find out the percentage of "Res" for all the records in January? PS - there is an abundance of extreme talent in this community! Thanks for all your postings! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Percentage of the occurance of a word in a date range
try =countif(A10:A900,"res") that will give you the number of times "res"
occurs, then divide it by the number of cells and display it as a percentage [RLK] Rollin' Like Kingz "T. Valko" wrote: Add an array to *each* SUMPRODUCT function like this: --(YEAR('Life Cycle'!A10:A900)=2008) Since you're testing for a specific year you don't have to be concerned about empty cells evaluating as month January. -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... The formula is great. I forgot to mention that some of the data is from last year. How can I tweak this formula to only show the "Res" for January of 2008 (excluding 2007)? Thanks, Art "T. Valko" wrote: I'm still having trouble The formula looks OK, what result do you get? -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... Valko, Thanks for the reply. I'm still having trouble (probably because I don't have a good grasp of arrays). I tweaked it a little because I am referencing a different worksheet named "Life Cycle". My data starts at row 10 and I need to caluclate all future entries, so I adjusted the range. However my data currently only has 150 rows. Any additional tips? Jeez, I'm dumb! =SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900="Res"))/SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900<"")) "T. Valko" wrote: Assuming there are no empty cells in the date range (empty cells will evaluate as month January). =SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<"")) Format as PERCENTAGE -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... I have a spreadsheet with dates in Column A, and either the word "Res" or "Comm" in Column M. How can I find out the percentage of "Res" for all the records in January? PS - there is an abundance of extreme talent in this community! Thanks for all your postings! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Percentage of the occurance of a word in a date range
I don't think that'll work.
You need to account for a specific time period. -- Biff Microsoft Excel MVP "Alan" wrote in message ... try =countif(A10:A900,"res") that will give you the number of times "res" occurs, then divide it by the number of cells and display it as a percentage [RLK] Rollin' Like Kingz "T. Valko" wrote: Add an array to *each* SUMPRODUCT function like this: --(YEAR('Life Cycle'!A10:A900)=2008) Since you're testing for a specific year you don't have to be concerned about empty cells evaluating as month January. -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... The formula is great. I forgot to mention that some of the data is from last year. How can I tweak this formula to only show the "Res" for January of 2008 (excluding 2007)? Thanks, Art "T. Valko" wrote: I'm still having trouble The formula looks OK, what result do you get? -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... Valko, Thanks for the reply. I'm still having trouble (probably because I don't have a good grasp of arrays). I tweaked it a little because I am referencing a different worksheet named "Life Cycle". My data starts at row 10 and I need to caluclate all future entries, so I adjusted the range. However my data currently only has 150 rows. Any additional tips? Jeez, I'm dumb! =SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900="Res"))/SUMPRODUCT(--(MONTH('Life Cycle'!A10:A900)=1),--('Life Cycle'!M10:M900<"")) "T. Valko" wrote: Assuming there are no empty cells in the date range (empty cells will evaluate as month January). =SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20="res"))/SUMPRODUCT(--(MONTH(A1:A20)=1),--(M1:M20<"")) Format as PERCENTAGE -- Biff Microsoft Excel MVP "Art-SNL" wrote in message ... I have a spreadsheet with dates in Column A, and either the word "Res" or "Comm" in Column M. How can I find out the percentage of "Res" for all the records in January? PS - there is an abundance of extreme talent in this community! Thanks for all your postings! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Indicating the first occurance of the max value in a range | Excel Worksheet Functions | |||
| Count Occurance of Text/Word in a Range | Excel Discussion (Misc queries) | |||
| Find last occurance of text in range | Excel Worksheet Functions | |||
| Completion Percentage of a date range | Excel Discussion (Misc queries) | |||
| occurance of numbers in cell range | Excel Worksheet Functions | |||
Linear Mode
