Percentage question

I am stuck on a percentage formula with a pass and fail % rating.
What confuses me is that I have to pull from multiple cells to get a correct %.

I am working on a spreadsheet for a communications company. The sheet is going to show Technicians work though the week that they accomplish or not.
Quality control personel will be monitoring their work. They are going to take X number of jobs daily to check. I need to figure out what the pass % of the jobs they complete and also fail.....but as I type in the numbers..the quanity of the work the quality check person takes and grades pass or fail is affecting the totals...I hope I can attach the start of the spread sheet made to show everyone excatly what I am asking of you....So sorry if confusing...ask any questions and I will do my best to clear them up.....

I'm sorry if I can't figure out what all you want "automated". I added a few formulas and those cells are now colored, but I'm trusting you can further show examples of numbers you want formulas for.

Originally Posted by berk21

So if Bob was given 10 jobs and 5 were completed, His percentage complete would be 50%.

The Quality Check personal takes 5 that were completed and only gets to 4 that day. So out of 4 he checks, 3 Fail. 25% pass 75% Fail. Simple..

But the QC % will alter when he doesn't check all that he takes.

You mention "percentage complete is 50%"...But your sheet has no point of reference for that piece of data, unless you want to convert column G to that function.

You need to provide at least ONE complete set of data and expected answers. I'm tripping over identifying your need. Don't bother with formulas, just put in data and what you expect to see as results and where.

Please fill out an entire set of data and answers, so we can see everything you're trying to accomplish. normally its obvious but your designs so far are not.

Total Jobs Qc'd % - what is this calculating against? The number in column I or column F? Compared to what...column E?

If you just put in a full set of answers and highlight the cells you want automated, this wouldn't be difficult.

I appreciate how pretty each version of the sheet is. Post #5 and post #7 each specifically asked for some complete data sets. The one you just posted has less than when you started.

Last try... 2 or 3 rows completely filled out by hand. Please.

Some portion of each line is data entries, and some part (the part you're looking for) are formulas. Your workbook doesn't make clear at all what the formulas should be based on the column headings.

If you know what you want, you should be able to make numeric entries on a few rows, and manually calculate whatever the result should be. Based on that, someone could help with the formulas. If you can't do that, no one is going to be able to help.

Yes, exactly like that. The column you left ?? in is unfortunate, since that's the most confusing one of all. That dataset is only 3 rows deep. You can't explain a solution for that column yet?

Anyway, the rest are simple and like the ones I posted in the first sample:

Please Login or Register  to view this content.

Stepping out an a limb here...try this formula in M10 and copy down. Does this do what you want?

=IF(C10<>"",L10/SUMIF($C$10:$F$34,C10,$G$10:$G$34),0)

It's summing all the Jobs given to the Tech for that line and dividing them into the number of Jobs that QC actually completed (L).

Close.

=IF(G13,H13/G13,0)

And I would do it this way, to make sure BOTH fields were ready to be audited:

=IF(AND(G10<>0,H10<>0),H10/G10,0)

So, (still begging for example numbers instead of just verbals), what is the answer for M10 based on everything you just said and the 3 rows of examples.

See, it's easy when you know how.

=SUMIF($C$10:$F$34,C10,$L$10:$L$34)/SUM($L$10:$L$34)

Well, I don't know. What you said (in simpler terms) is compare the answer in column L against the entire sum of all the answers in column L.

Dude, you gotta read the formulas. A full data set starts with a name. Put a name in and your formulas activate.

Here, I changed the cell check, so it watches K & L instead of C. Similar change in P. Hope this settles it for you.

Check the latest version I posted in #26.

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]