Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Tracking activities
Hi,
I have an excel table with following fields and sample data DOCNAME NUMPAGES PERSON %COMPLETE Doc1 20 A 50 Doc2 52 B 30 Doc3 26 D 60 Doc4 60 A/C/D 80 Doc5 15 B/C 100 ...etc.. I need to make a summary sheet containing how much each person has completed, like: PERSON %COMPLETED A ?? <-- X B ?? C ?? D ?? The way to compute X would be: Sum (NUMPAGES * %COMPLETED * Factor) for all rows where A is the person. Factor is calculated as: 1, if 1 person is doing it 1/2, if 2 person is doing it 1/3, if 3 person is doing it I know about implementing multiple conditions in SUMPRODUCT, but unable to understand how to implement this. Any clues would be appreciated! Regards, ~Animesh |
#2
|
|||
|
|||
Hi
try the following: 1. Create a helper column with the following formula to calculate the number of involved persons: =LEN(C1)-LEN(SUBSTITUTE(A1,"/",""))+1 and copy this for all rows. Lets assume this is in column E then use the following formula =SUMPRODUCT(--(ISNUMBER(FIND("A",C1:C100)),B1:B100,C1:C100,(1/E1:E100)) "Animesh" wrote: Hi, I have an excel table with following fields and sample data DOCNAME NUMPAGES PERSON %COMPLETE Doc1 20 A 50 Doc2 52 B 30 Doc3 26 D 60 Doc4 60 A/C/D 80 Doc5 15 B/C 100 ...etc.. I need to make a summary sheet containing how much each person has completed, like: PERSON %COMPLETED A ?? <-- X B ?? C ?? D ?? The way to compute X would be: Sum (NUMPAGES * %COMPLETED * Factor) for all rows where A is the person. Factor is calculated as: 1, if 1 person is doing it 1/2, if 2 person is doing it 1/3, if 3 person is doing it I know about implementing multiple conditions in SUMPRODUCT, but unable to understand how to implement this. Any clues would be appreciated! Regards, ~Animesh |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where is the Template Wizard with Data Tracking Excel 2003? | Excel Discussion (Misc queries) | |||
Tracking Sports Stats | Excel Discussion (Misc queries) | |||
Planning & Tracking Home-work projects for kids. | Excel Discussion (Misc queries) | |||
How do I convert an existing MS Excel worksheet tracking a simple. | Excel Discussion (Misc queries) | |||
Is there a template for legislative tracking in excel? | Excel Discussion (Misc queries) |