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

Attached Files

Copy of 0a1_WEEK BY WEEK_from forum.xls (413.0 KB, 11 views)

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]

Cross post http://www.excelguru.ca/forums/showt...by-week-report

__________________
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

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]