| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting number of weeks to complete a task
Hi,
Can anyone help me please. I have a spreadsheet and I am trying to calculate how many weeks a task took to complete. The task does not necessairly have to be completed in consecutive order. A brief example: Week Task 1 Painting 1 Painting 1 Drawing 2 Drawing 2 Drawing 2 Drawing 3 Painting 3 Drawing 3 Drawing From the table the Painting task was completed in 2 weeks and the Drawing task was completed in 3 weeks. I would appreciate your help. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of weeks to complete a task
Try this:
=SUM(N(FREQUENCY(IF(Task="Painting",MATCH(Week,Wee k,0)),MATCH(Week,Week,0))0)) =SUM(N(FREQUENCY(IF(Task="Drawing",MATCH(Week,Week ,0)),MATCH(Week,Week,0))0)) Both formulas are required with CRTL+SHIFT+ENTER, not just ENTER " wrote: Hi, Can anyone help me please. I have a spreadsheet and I am trying to calculate how many weeks a task took to complete. The task does not necessairly have to be completed in consecutive order. A brief example: Week Task 1 Painting 1 Painting 1 Drawing 2 Drawing 2 Drawing 2 Drawing 3 Painting 3 Drawing 3 Drawing From the table the Painting task was completed in 2 weeks and the Drawing task was completed in 3 weeks. I would appreciate your help. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of weeks to complete a task
|
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of weeks to complete a task
Assume:
A2:A10 = Week B2:B10 = Task D2:D3 = Painting; Drawing If there are no empty cells within Week, try this arry formula** : =COUNT(1/FREQUENCY(IF(Task=D2,Week),Week)) If there might be empty cells within Week, use this version** : =COUNT(1/FREQUENCY(IF((Task=D2)*(Week<""),Week),Week)) Copy down to D3. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP wrote in message ... Hi, Can anyone help me please. I have a spreadsheet and I am trying to calculate how many weeks a task took to complete. The task does not necessairly have to be completed in consecutive order. A brief example: Week Task 1 Painting 1 Painting 1 Drawing 2 Drawing 2 Drawing 2 Drawing 3 Painting 3 Drawing 3 Drawing From the table the Painting task was completed in 2 weeks and the Drawing task was completed in 3 weeks. I would appreciate your help. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of weeks to complete a task
On Apr 14, 3:44*am, "T. Valko" wrote:
Assume: A2:A10 = Week B2:B10 = Task D2:D3 = Painting; Drawing If there are no empty cells within Week, try this arry formula** : =COUNT(1/FREQUENCY(IF(Task=D2,Week),Week)) If there might be empty cells within Week, use this version** : =COUNT(1/FREQUENCY(IF((Task=D2)*(Week<""),Week),Week)) Copy down to D3. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP wrote in message ... Hi, Can anyone help me please. I have a spreadsheet and I am trying to calculate how many weeks a task took to complete. The task does not necessairly have to be completed in consecutive order. A brief example: Week * Task 1 * * * * *Painting 1 * * * * *Painting 1 * * * * *Drawing 2 * * * * *Drawing 2 * * * * *Drawing 2 * * * * *Drawing 3 * * * * *Painting 3 * * * * *Drawing 3 * * * * *Drawing From the table the Painting task was completed in 2 weeks and the Drawing task was completed in 3 weeks. I would appreciate your help.- Hide quoted text - - Show quoted text - Thanks everyone! You are lifesavers! Biff's worked the best! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of weeks to complete a task
wrote in message
... On Apr 14, 3:44 am, "T. Valko" wrote: Assume: A2:A10 = Week B2:B10 = Task D2:D3 = Painting; Drawing If there are no empty cells within Week, try this arry formula** : =COUNT(1/FREQUENCY(IF(Task=D2,Week),Week)) If there might be empty cells within Week, use this version** : =COUNT(1/FREQUENCY(IF((Task=D2)*(Week<""),Week),Week)) Copy down to D3. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP wrote in message ... Hi, Can anyone help me please. I have a spreadsheet and I am trying to calculate how many weeks a task took to complete. The task does not necessairly have to be completed in consecutive order. A brief example: Week Task 1 Painting 1 Painting 1 Drawing 2 Drawing 2 Drawing 2 Drawing 3 Painting 3 Drawing 3 Drawing From the table the Painting task was completed in 2 weeks and the Drawing task was completed in 3 weeks. I would appreciate your help.- Hide quoted text - - Show quoted text - Thanks everyone! You are lifesavers! Biff's worked the best! You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Counting number of weeks to complete a task
Hi,
You may also use the SUMIF() formula as follows: =SUMIF(B1:B9,"="&A11,A1:A9) A11 has either Painting or drawing. -- Regards, Ashish Mathur www.ashishmathur.com "T. Valko" wrote in message ... wrote in message ... On Apr 14, 3:44 am, "T. Valko" wrote: Assume: A2:A10 = Week B2:B10 = Task D2:D3 = Painting; Drawing If there are no empty cells within Week, try this arry formula** : =COUNT(1/FREQUENCY(IF(Task=D2,Week),Week)) If there might be empty cells within Week, use this version** : =COUNT(1/FREQUENCY(IF((Task=D2)*(Week<""),Week),Week)) Copy down to D3. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP wrote in message ... Hi, Can anyone help me please. I have a spreadsheet and I am trying to calculate how many weeks a task took to complete. The task does not necessairly have to be completed in consecutive order. A brief example: Week Task 1 Painting 1 Painting 1 Drawing 2 Drawing 2 Drawing 2 Drawing 3 Painting 3 Drawing 3 Drawing From the table the Painting task was completed in 2 weeks and the Drawing task was completed in 3 weeks. I would appreciate your help.- Hide quoted text - - Show quoted text - Thanks everyone! You are lifesavers! Biff's worked the best! You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel cannot complete this task with available resoucres | Excel Discussion (Misc queries) | |||
| Excel cannot complete this task with available resources. | Excel Discussion (Misc queries) | |||
| Excel cannot complete this task | Excel Discussion (Misc queries) | |||
| how do you put a complete task check box on excel | New Users to Excel | |||
| Sharing Worksheet cannot complete task | Excel Worksheet Functions | |||
Linear Mode
