More a general maths problem than Excel specific, but would love some input!

Hello,

I'm creating a simple spreadsheet to aid a colleague in auditing some work generated by his team.
There are seven elements in total, with six having three measures and one having just two. In most cases the elements are a simple ‘yes / no’ (marked as as 1 and 0), and the third element being a simple percentage recorded as a decimal, i.e 1, 0.8, 0.25 etc.

EG

Measure 1 - Present (1) Valid (1) Effectiveness (1) = total 3 out of possible 3
Measure 2 - Present (1) Valid (0) Effectiveness (0) = total 1 out of possible 3
Measure 3 - Present (1) Valid (1) Effectiveness (0.3) = total 2.3 out of possible 3

etc etc.... for an undefined number of records (anywhere between 1-10 initially). As you can see, it's simple enough and easy to record and capture (an essential requirement in the circumstances). So far so good.

My problem is how to then process the totals to create a fair comparison between records audited and between staff members within the team. My initial plans didn't work out, as I thought of multiplying the sum of the Measure 1 element, with those of Measure 2 and Measure 3 before dividing by the sample size. Clearly that would not work as I figured out, but I admit to hoping I'd stumble across something else whilst tinkering!

I've attached a rough outline as to what I'm hoping to achieve, with cell E21 showing my failed formula and I've populated it with 3 sample score profiles. Having an overall per learner score is easily achieved (column D) but it needs to measure staff effectiveness for each category too.

So, how can I end up with scores which are a fair comparison regardless of having 10 files sampled or 2? The value generated for each audited measure will be eventually used as a metric for targets and performance reviews, so it needs to be fair (as much as it can be with varied sample quantities, I appreciate that), but as one sampled record has no bearing on the next one etc. the scores should all achieve a certain value as a minimum anyway.

Thanks for any insight, if you have questions please ask and I'll reply as soon as I can.

Dabooka

Problem solved.

Mods you can delete the thread if you wish as as it didn't attract any replies anyway.

Cheers,

Dabooka