I need help understanding how to accurately create a formula to calc overall passing %.

[Shamackdvd]

Capture2.PNG

Other info from pick The spreadsheet covers Call 1 (from left) All the way to call 8

Question #1 : What formula would i use to calculate the overall pass rate forr calls monitored as we key in the scores. The field used to determine this in the picture is Pass/ Fail. I am currently using the following formula but when i use the calculator it is coming up with a different passing rate than whats showing in excel with the formula i am currently using. Calculator shows % and excel formula I'm using is showing 38.89% but calculator shows 44.44%. 8 out of 18 calls show passed in the field. The current formula used is {=SUMPRODUCT(--($D$20:$D$28))/COUNTA(D20:D28)}

Question #2: Is there anyway to have the auto fail field, the criticals field, and majors field create an auto respone for pass/fail fields? So example if i key the number 1 or higher for auto fails, 2 or higher for criticals, is there anyway to have it automatically update the pass/fail field to reflect "fail".

[AliGW]

Please attach a sample worksheet with expected outcomes and instructions added manually. The screen shots are, in any case, rather too small to decipher.

[Shamackdvd]

see attachment below

[Shamackdvd]

Testing.xlsx

here is excel 2010 document attachment

[AliGW]

In which field does it show that 8/18 calls passed? I can only count 6. What formula are you using to calculate the pass rate for each sales rep? Why have you not left the formulae in the copy of the workbook? It's quite hard to understand what is meant to be happening as it is.

[Shamackdvd]

H8:16, t8:t16, af8:af16, ar8:ar16

[Shamackdvd]

yes it should be 6/14 which should calculate as 42.85%, but is showing under Transitioning Agents Passing Rate (D30) as 38.89%
the formula used under (D30) is =SUMPRODUCT(--($D$20:$D$28))/COUNTA(D20:D28)

[FlameRetired]

If I am interpreting correctly try array-entering this formula in D20 and filling down to D28.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[FlameRetired]

My previous formula can be made simpler. Try this in D20:D28. It does not have to be array-entered.

Please Login or Register  to view this content.