Using SUMPRODUCT to create a weighted score

mflatt23

New Member
Joined
Jun 2, 2015
Messages
22
Hello everyone,

I've been working on a stock analysis spreadsheet and am getting a little stuck trying to create a weighted score. In the spreadsheet linked below, I've got a couple of columns:

- Answer (this is a "Y" or "N")
- Score (if "Y," then 1; if "N," then 0)
- Weight (either 1 or 2..might change these up later)

In cell C49, I've got the Total Score as a percentage of "Y" answers. I'd like to integrate a Weighted Score in cell D49, but I'm getting stuck using the SUMPRODUCT formula to make it a percentage like Total Score.

Any help/direction would be much appreciated!

Spreadsheet link: Upload files for free - Stock Research.xlsx - ufile.io
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Try:

ABCD
1
2AnswerScoreWeight
3N02
4Y11
5Y11
6Y12
7Y11
8Y12
9Y11
10N01
11Y12
12Y11
13Y12
14Y11
15Y11
16Y11
17Y12
18Y12
19Y11
20Y11
21Y11
22Y11
23Y11
24Y11
25Y11
26Y11
27Y11
28Y11
29Y11
30Y11
31Y11
32Y12
33Y11
34Y11
35Y11
36Y11
37Y11
38
39
40Weighted score93.0%
41OR93.0%
42
Sheet1
Cell Formulas
RangeFormula
C3:C37C3=IF(B3="N",0,1)
C40C40=SUMPRODUCT(C3:C37,D3:D37)/SUM(D3:D37)
C41C41=SUMPRODUCT(--(B3:B37="Y"),D3:D37)/SUM(D3:D37)
 
Upvote 0

Forum statistics

Threads
1,223,157
Messages
6,170,420
Members
452,325
Latest member
BlahQz

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