Weighted excel formula yes,no,na

[wjohn47]

I'm working on a contact center quality score card that has yes, no, and na answers. each line item has a different weight with lines e12,and e13 if answered no should result in the entire form auto zero regardless of the other answers, if answered yes will be valued at 15 points each. The na answers should not reduce score they have no value. no value would be 0 and yes value. can someone help create the syntax needed.[TABLE="width: 823"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]grade[/TD]
[TD]weight[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Question[/TD]
[TD]Answer[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Greeting[/TD]
[TD="colspan: 3"]Did the agent apply an applicable greeting?[/TD]
[TD]Yes[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Was the agent readily available to service call upon presenting?[/TD]
[TD]Yes[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent demonstrate willingness to assist the caller?[/TD]
[TD]Yes[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Handle Contact[/TD]
[TD="colspan: 3"]Did the agent obtain caller's name and good call back number with ext if applicable? [/TD]
[TD]Yes[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent verify the appropriate Provider Verification Procedures (i.e. National Provider Identifier (NPI) or Tax Provider Identification) and facility/provider?[/TD]
[TD]Yes[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent verify the member demographics? (i.e. Full name, DOB and ID number)[/TD]
[TD]Yes[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Transaction Information[/TD]
[TD="colspan: 3"]Did the agent properly acknowledge the caller's request?[/TD]
[TD]Yes[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent properly provide the requested information?[/TD]
[TD]N/A[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent offer the most appropriate solution to meet caller's needs?[/TD]
[TD]N/A[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="colspan: 3"] Did the agent provide the Member's Claims Paid Through Date? [/TD]
[TD]N/A[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent provide the Member's Deductible and Out Of Pocket Maximum? [/TD]
[TD]N/A[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent provide correct grace period if applicable? [/TD]
[TD]N/A[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent provide the correct benefits for the member?[/TD]
[TD]N/A[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Telephony Skills[/TD]
[TD="colspan: 3"]Did the agent ask and then wait for the caller's approval to place them on hold? [/TD]
[TD]N/A[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent advise the caller that the call was being transferred and why?[/TD]
[TD]N/A[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Soft Skills[/TD]
[TD="colspan: 3"]Was the agent courteous and professional throughout the call? (i.e. Did not interrupt caller or use slang)?[/TD]
[TD]N/A[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent use the caller's name at least once?[/TD]
[TD]N/A[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent actively listen and adapt to the caller's speed on handling?[/TD]
[TD]N/A[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent sound friendly/polite and welcoming?[/TD]
[TD]N/A[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did agent sound clear and confident throughout the call?[/TD]
[TD]N/A[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent avoid long silences during the call? [/TD]
[TD]Yes[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]End Call[/TD]
[TD="colspan: 3"]Did the agent service the call appropriately based on the provider's need? [/TD]
[TD]Yes[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent properly document the call interaction in QNXT and accurately provide a Reference Number? [/TD]
[TD]Yes[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent offer further assistance at the end of the call?[/TD]
[TD]Yes[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Did the agent close the call in an appropriate manner?[/TD]
[TD]Yes[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]

 

[wjohn47]

Re: Weighted excel formula yes,no,na Help please

ADDITIONAL INFORMATION: The first questions starts in cell e8 the last question ends on cell e32

 

[Murphy123]

Re: Weighted excel formula yes,no,na Help please

ADDITIONAL INFORMATION: The first questions starts in cell e8 the last question ends on cell e32

Hi

Try This

=IF(OR(E12="No";E13="No");0;SUMPRODUCT
(--(E8:E32="Yes");E8:E32))

Regards
Murphy123

 

[Murphy123]

Re: Weighted excel formula yes,no,na Help please

Hi

Just realized I typed that formula incorrectly
should be

=IF(OR(E12="No";E13="No");0;SUMPRODUCT
(--(E8:E32="Yes");F8:F32))

Regards
Murphy123

 

[wjohn47]

Re: Weighted excel formula yes,no,na Help please

Hi

Just realized I typed that formula incorrectly
should be

=IF(OR(E12="No";E13="No");0;SUMPRODUCT
(--(E8:E32="Yes");F8:F32))

Regards
Murphy123

I tried the formula you supplied, and received an error message. i have a current formula in place but this does not add specific weight to the line items it just divides each line evenly ( # of line items, divided by 100)
=IF(COUNTIF(E12:E13,"No")>0,0,COUNTIF(E8:E32,"Yes")/(COUNTIF(E8:E32,"Yes")+COUNTIF(E8:E32,"No")))

Is there a way you can work off this formula? or how can i alter this currently formula to make it do the weights that i want for each line?