Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Analysis
I have a large worksheet that I need to analyze a multiple set of
conditions, and return a count based on the analysis. I have exhausted my knowledge on how to accomplish this. I have tried variations of COUNTIF and Arrays, with no luck. A PIVOTTABLE works but multiple people with no knowledge of Excel view the results and do not know how to manipulate the data. So I have opted to report the results in table format with a worksheet for each month. What I need is: How many people in October, assigned to a certain team, peroformed an action, within a specific time frame. There are 12 months, 6 teams, 13 actions, 2 time frames that must be analyzed with the data coming from one worksheet. The results are reported as a worksheet for each month, actions as rows, and teams as merged columns with time frames as the sub-columns. Any help would be greatly appreciated. I would like to do it as a formula vice VBA, since my knowledge of VBA is weak. Thank you, Randy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Analysis
Hi Randy
if pivot tables work I would try to train your colleagues :-) But if you have to use formulas maybe using SUMPRODUCT could help you. e.g. use the following for counting based on multiple criteria: =SUMPRODUCT((A1:A1000=criteria_1)*(B1:B1000=criter ia_2)*(C1:C1000=crite ria_3)) to sum a range based on multiple criteria use =SUMPRODUCT((A1:A1000=criteria_1)*(B1:B1000=criter ia_2)*(C1:C1000=crite ria_3),D1:D1000) - sums columns D HTH Frank Randy wrote: I have a large worksheet that I need to analyze a multiple set of conditions, and return a count based on the analysis. I have exhausted my knowledge on how to accomplish this. I have tried variations of COUNTIF and Arrays, with no luck. A PIVOTTABLE works but multiple people with no knowledge of Excel view the results and do not know how to manipulate the data. So I have opted to report the results in table format with a worksheet for each month. What I need is: How many people in October, assigned to a certain team, peroformed an action, within a specific time frame. There are 12 months, 6 teams, 13 actions, 2 time frames that must be analyzed with the data coming from one worksheet. The results are reported as a worksheet for each month, actions as rows, and teams as merged columns with time frames as the sub-columns. Any help would be greatly appreciated. I would like to do it as a formula vice VBA, since my knowledge of VBA is weak. Thank you, Randy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Analysis ToolPak Regression Analysis Help Requested | Excel Worksheet Functions | |||
Analysis Toolpak-Confidence Level and data analysis questions | Excel Worksheet Functions | |||
Analysis | Excel Worksheet Functions | |||
Why " data analysis plus " override " data analysis " once instal. | Excel Worksheet Functions | |||
Analysis ToolPak installed but no Data Analysis option | Excel Discussion (Misc queries) |