Excel 2007 : formula to average a percentage

on the attached spreadsheet I am looking to get the correct formula to average a percentage.

column P10:P34 shows a pass fail %.
I was wondering if column H and L can be included to some what average the failure rate instead of basing it on just 2 cells (current formula)...giving column P more of a all around answer.

ie.column H,L,N,O= P

Once again, it is really up to you to define a simple explanation of what you want to happen in any one cell. Once again I'm guessing at what you're talking about.

Currently, the QC Failure % is a comparison of how many failed out of how many that were QC'd. That makes sense as a percentage. 20 Qc'd (L10), 11 failed (O10), so QC Failure % of 55%...clear.

So, how can the total jobs completed in cell H10 be included in the same cell in a meaningful way? Aren't you really looking for another percentage concept altogether?

Taking the 25 completed jobs (H10) and the QC failure number (O10) and comparing them seems wrong since the QC people haven't QC'd all 25 jobs. How can it be right to do that?

Anyway, having said all that, if you really want to do that, simply =O10/H10 gives you that percentage.

If that's not what you want, instead of explaining it, do the math one time yourself and show us the answers you want for one or two rows, then explain it. We can create formulas from clear sample results much faster than anything else.

Also, while waiting for your response, all the "collection" formulas in your WeeklyReview sheet can benefit from simple 3d-Formulas.

Your current formula in G10 of:
=Monday!G10+Tuesday!G10+Wednesday!G10+Thursday!G10+Friday!G10+Saturday!G10+Sunday!G10

...can be replaced with the 3d-reference of:
=SUM(Monday:Sunday!G10)
...and copied down. That works for G,H,I,K and L.

Originally Posted by berk21

If you were to do 10 fantastic jobs and 2 were judged horrible....is it fair to give the final review based on just those 2? not really...

And I would disagree. Spreadsheets aren't usually about fairness. Spreadsheets are unemotional depiction of facts.

10 awesome jobs and 2 horrible jobs is a 17% horrible rate. That's what it is. (2/12=.1666667)

Worse, so far, the 2 horrible may be out of only half the completed jobs having been QC'd yet, so this employee's true failure rate remains to be seen.

As I said, if you want to present other statistics, then do so in another cell. What you have in the sheet so far is barely understandable, but it IS understandable.

...what if 8 bad 2 good...judging the 2 you would look great...

Again I would disagree. No statistical representation of performance would say 80% failure rate is "great". Would it?

So maybe what I am asking is, taking the average work completed and qc's completed and comparing it to the Failure rate, I can get an average of failure rate.

Will this work? L10/J10=28% out of that (O10)11 failed, so 11/28=39% Failure

Not following any of that. J10 is a percentage already.

Anything you want to do will work. Figure out an example of what you do want, and show us example(s) of the resulting numbers. These "what ifs" aren't helping since logically I don't see what you're attempting.

If it's me being dense, I apologize, but I see working formulas now and what you're describing doesn't jive for me.

You're officially stumping me. I've read those sentences 10 times and admit defeat. I can't conceive what you're saying, so I'm going to set it aside and look at your sheet for some sort of reference to how many failed QC for one tech can be "percentage" against everyone. I don't even understand it when I say it, but try this over in S10:

=O10/SUM($L$10:$L$34)
or
=O10/$L$36

Jason failed 11 times out of the 143 QC'd currently on the sheet for everyone.

EDIT:

Since column M is a reference to one tech against ALL techs, then it goes to reason that the second percentage (column P) does the same thing.

is it hard as far as the way I am wording it?

Analogy maybe? can I relate it to a job you do to make it reasonably understandable to you?
I am trying my hardest to help you visualize...and I gave you cells and some equations like you wanted....What can I do to help you see what I see?

For understanding? No. But here's a formula to try since I don't know what answer you expect.

=O10/M10/100