Weighted excel formula yes,no,na

wjohn47

New Member
Joined
Nov 30, 2017
Messages
3
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.
gradeweight
QuestionAnswer
GreetingDid the agent apply an applicable greeting?Yes3
Was the agent readily available to service call upon presenting?Yes5
Did the agent demonstrate willingness to assist the caller?Yes2
Handle ContactDid the agent obtain caller's name and good call back number with ext if applicable? Yes1
Did the agent verify the appropriate Provider Verification Procedures (i.e. National Provider Identifier (NPI) or Tax Provider Identification) and facility/provider?Yes15
Did the agent verify the member demographics? (i.e. Full name, DOB and ID number)Yes15
Transaction InformationDid the agent properly acknowledge the caller's request?Yes1
Did the agent properly provide the requested information?N/A1
Did the agent offer the most appropriate solution to meet caller's needs?N/A1
Did the agent provide the Member's Claims Paid Through Date? N/A5
Did the agent provide the Member's Deductible and Out Of Pocket Maximum? N/A5
Did the agent provide correct grace period if applicable? N/A10
Did the agent provide the correct benefits for the member?N/A10
Telephony SkillsDid the agent ask and then wait for the caller's approval to place them on hold? N/A3
Did the agent advise the caller that the call was being transferred and why?N/A1
Soft SkillsWas the agent courteous and professional throughout the call? (i.e. Did not interrupt caller or use slang)?N/A5
Did the agent use the caller's name at least once?N/A2
Did the agent actively listen and adapt to the caller's speed on handling?N/A1
Did the agent sound friendly/polite and welcoming?N/A5
Did agent sound clear and confident throughout the call?N/A2
Did the agent avoid long silences during the call? Yes2
End CallDid the agent service the call appropriately based on the provider's need? Yes1
Did the agent properly document the call interaction in QNXT and accurately provide a Reference Number? Yes1
Did the agent offer further assistance at the end of the call?Yes2
Did the agent close the call in an appropriate manner?Yes1
100

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top