I have a workbook that keeps an up date of where we are with Audit action points throughout the business.
What I am trying to do is to analyse the data in different views.
Each audit area may have separate scoring items. Each scoring item may result in a different Level.(Sheet 1)
I know this can easily be done with a pivot table but I require a formula that will allow me to extract the data into another worksheet. (Sheet 2)
For each Audit Area Name I need a count of the Level but based on the worst result outcome for each Scoring Outcome.
For example in Delegated Authorities for level 3 A.0.3.1 the worst outcome would be maybe/not sure. And the total for delegated Authorities for level 3 would be Yes = 3, Maybe/Not Sure = 1 & Cannot predict FCA testing outcome = 1.
I hope this makes sense.
Please e gentle as it is my first post.
Last edited by MHarris; 09-10-2009 at 09:02 AM.
Not certain I am fully understadning your requirement, but see if the attached is helpful and headed in the right direction.
The SUMPRODUCT formula is used - sheet3.
=SUMPRODUCT(--(AreaName=$A$10),--(ScoreItem=$B10),--(Level=$A$1))
Thanks for that reply but that isn't really the answer I am after.
If we take for instance the result for scoring item B.0.3.1 then there are 2 occurances of this scoring item, I only require the result of the lowest score of the 2 results. (Thus the answer would be a count of one against Level 2.
Where we would have a answer of 6 against Level 1 for scoring item A.1.3.1 then the count I would be looking for is 1 against Level 1, being the lowest result for this item.
The result would then be sumed to results table in tab sheet 2.
Thanks in advance for your help
Mark
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks