Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Formula???
I want to export a query from Access to Excel and then analyze the data. What I'm pulling into Excel is data with 31 columns. (30 are audit questions and the last is the name of the person being audited.) Row 1 is a header row, showing each question's description. (i.e. Greeting, time check, transfer, etc.) The remaining rows are either 1, 0 or -1 for the 30 questions, and the person's name in the last column. ("1" is considered a pass, "0" is considered a fail, and "-1" is considered not applicable.) The purpose of the analysis is to identify all questions scored as "0" for each person. The problem is, each person has more than one audit, so I guess I need some kind of lookup formula that will look at the scores for all of each person's audits and return the question description for all cases of a "0" score. For instance, John Doe scored a "0" on Greeting in three different audits, but only one "0" on Transfer. I would want the formula to produce the following results: John Doe: Greeting Transfer Greeting Greeting We would then analyze the data each month, allowing us to identify trends. (i.e. John Doe scores "0" on greeting several times each month - maybe additional training is needed for John on greeting requirements.) If anyone can assist, I would greatly appreciate it. If needed, I can provide further information. -- mkuethe ------------------------------------------------------------------------ mkuethe's Profile: http://www.excelforum.com/member.php...o&userid=34568 View this thread: http://www.excelforum.com/showthread...hreadid=543319 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Formula???
You could use the SUMPRODUCT formula to help you with this. Assume that Sheet1!Column A is greeting and Sheet1!Column D is the persons name Setup a similar table on Sheet2 but with only one row per person rather than multiple and it would be something like this... =SUMPRODUCT(--(Sheet1!$A:$A=0),--(Sheet1!$D:$D=$D2)) That should return the count of all '0' for greeting for the person in D2. You can copy and paste that formula down for each person and category and get the results. While it won't give you Greeting Transfer Greeting Greeting It will give you Greeting Transfer Name 3 1 Smith 2 0 Jones -- kraljb ------------------------------------------------------------------------ kraljb's Profile: http://www.excelforum.com/member.php...fo&userid=9955 View this thread: http://www.excelforum.com/showthread...hreadid=543319 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Formula???
I'm getting a !NUM error. -- mkuethe ------------------------------------------------------------------------ mkuethe's Profile: http://www.excelforum.com/member.php...o&userid=34568 View this thread: http://www.excelforum.com/showthread...hreadid=543319 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Formula???
=SUMPRODUCT(--(Sheet1!$A1:$A1000=0),--(Sheet1!$D1:$D1000=$D2))
Note that SUMPRODUCT doesn't work with complete columns, you have to specify a range. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "mkuethe" wrote in message ... I'm getting a !NUM error. -- mkuethe ------------------------------------------------------------------------ mkuethe's Profile: http://www.excelforum.com/member.php...o&userid=34568 View this thread: http://www.excelforum.com/showthread...hreadid=543319 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Match then lookup | Excel Worksheet Functions | |||
How do I lookup a value in a array that is not in ascending order | Excel Worksheet Functions | |||
formula to lookup table in another Worksheet please | Excel Worksheet Functions | |||
How do I have a formula lookup a sheetname? | Excel Worksheet Functions |