#1
|
|||
|
|||
Week by week report
Hi All,
I have a spreadsheet which works great but I want to add some additional formulas and need a little help? There are 2 tabs with this spreadsheet. The first tab is raw data which shows name, date of audit and audit score. This data contains all audit scores for all members of staff . The second tab actually shows an average audit score of the data week by week for each person. Is there a way to add another column next to the percentage to show the number of audits completed? So this would be a count of the entries rather than an average? So for example, column D would not show the amount of audits completed. Eg Derek Spenser between 30th Sept and 4th Oct = 4 I have attached another example. Hope this makes sense? Many thanks Craig |
#2
|
||||
|
||||
The correct percentage would presumably be:
=IF(SUMPRODUCT((Data!$B$2:$B$8090=$B8)*(Data!$C$2: $C$8090>=C$5)*(Data!$C$2:$C$8090<=C$6)*(Data!$D$2: $D$8090>0))=0,0,SUMPRODUCT((Data!$B$2:$B$8090=$B8) *(Data!$C$2:$C$8090>=C$5)*(Data!$C$2:$C$8090<=C$6) *(Data!$D$2:$D$8090=1))/SUMPRODUCT((Data!$B$2:$B$8090=$B8)*(Data!$C$2:$C$8 090>=C$5)*(Data!$C$2:$C$8090<=C$6)*(Data!$D$2:$D$8 090>0))) To get the actual counts (both # completed and # in progress), you could use: =SUMPRODUCT((Data!$B$2:$B$8090=$B8)*(Data!$C$2:$C$ 8090>=C$5)*(Data!$C$2:$C$8090<=C$6)*(Data!$D$2:$D$ 8090=1)) &"/"&SUMPRODUCT((Data!$B$2:$B$8090=$B8)*(Data!$C$2:$C $8090>=C$5)*(Data!$C$2:$C$8090<=C$6)*(Data!$D$2:$D $8090>0))
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] Last edited by macropod; 11-15-2013 at 10:43 PM. Reason: Formula revision |
#3
|
||||
|
||||
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
||||
|
||||
Craig,
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184 Note that the link is to the same board that you cross-posted at!
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
send an email every week. | devilonline | Outlook | 0 | 08-03-2012 10:30 AM |
5 days/week during planning & 7 days/week during implementation | sanlen | Project | 1 | 06-25-2012 04:17 PM |
Conditional Formatting per Week | mrgy05 | Excel | 2 | 06-07-2012 10:38 AM |
How to reorder the days of the week | Mosey | Outlook | 2 | 10-31-2011 06:47 AM |
Calander work week | cfawcett22 | Outlook | 1 | 08-06-2011 01:46 PM |