problem using sum for weighed averages

[elitekatti]

Hello,

My requirement is to add sum of multiple rows which have individual variable weights.
For example, row 1 cell has value 75 and its weight is 25%. Similarly row 2's cell has value say 100 and its weight is 40% and so on..
I need sum of all these rows.

I also need to keep in mind that a value 'N/A' (any combinations-uppercase or lowercase) can also be entered for any row values.
In that case, the value should NOT be ignored but value be considered as 1.

awaiting your suggestions here...
Thanks in advance!

 

[Aladin Akyurek]

Try to post a small sample along with the desired result.

 

[elitekatti]

@Aladin: Thanks for the input !
Well, the example would be as mentioned below:


English Maths Social-Science Drawing Crafts Total
Alex 80 60 75 80 90
Bob 75 95 92 72 80
Carl 80 85 95 65 85

English Maths Social-Science Drawing Crafts Total Percentage
Weights for each subject 20 30 30 10 10 100




Note: Sorry, I wanted to edit this in my question! But did not know how to do it.
So here is the change in the question - The first part is same. Only the part where I talk about N/A values needs to be changed.
In case of rows having N/A, the remaining rows percentage should be adjusted accordingly so that the final weight is still 100%.
Meaning: Assume in above example, for Bob - 'Drawing' row value is N/A, then for him remaining subjects' weights should be adjusted equally such that their weights still add up to 100%.
In this case for Bob subject weights would be : English=22.5; Maths=32.5;Social Science=32.5; Drawing=0; Crafts=12.5;


I hope the above example is understandable...


Thank you!

 

[Aladin Akyurek]

@Aladin: Thanks for the input !
Well, the example would be as mentioned below:


English Maths Social-Science Drawing Crafts Total
Alex 80 60 75 80 90
Bob 75 95 92 72 80
Carl 80 85 95 65 85

English Maths Social-Science Drawing Crafts Total Percentage
Weights for each subject 20 30 30 10 10 100




Note: Sorry, I wanted to edit this in my question! But did not know how to do it.
So here is the change in the question - The first part is same. Only the part where I talk about N/A values needs to be changed.
In case of rows having N/A, the remaining rows percentage should be adjusted accordingly so that the final weight is still 100%.
Meaning: Assume in above example, for Bob - 'Drawing' row value is N/A, then for him remaining subjects' weights should be adjusted equally such that their weights still add up to 100%.
In this case for Bob subject weights would be : English=22.5; Maths=32.5;Social Science=32.5; Drawing=0; Crafts=12.5;


I hope the above example is understandable...


Thank you!

No, it's not...

Is this what you are expecting?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]English[/td][td]Maths[/td][td]Social-Science[/td][td]Drawing[/td][td]Crafts[/td][td]Total[/td][td]Percentage[/td][/tr]

[tr][td]

2​

[/td][td]Alex[/td][td]

80​

[/td][td]

60​

[/td][td]

75​

[/td][td]

80​

[/td][td]

90​

[/td][td][/td][td]

73.5​

[/td][/tr]

[tr][td]

3​

[/td][td]Bob[/td][td]

75​

[/td][td]

95​

[/td][td]

92​

[/td][td]

72​

[/td][td]

80​

[/td][td][/td][td]

86.3​

[/td][/tr]

[tr][td]

4​

[/td][td]Carl[/td][td]

80​

[/td][td]

85​

[/td][td]

95​

[/td][td]

65​

[/td][td]

85​

[/td][td][/td][td]

85​

[/td][/tr]
[/table]

 

[KyleUK]

[TABLE="width: 459"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]10%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]English[/TD]
[TD]Maths[/TD]
[TD]Social-Science[/TD]
[TD]Drawing[/TD]
[TD]Crafts[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]73.5[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]86.3[/TD]
[/TR]
[TR]
[TD]Carl[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]95[/TD]
[TD]N/A[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]87.125[/TD]
[/TR]
</tbody>[/TABLE]

Does this represent what you'd expect?

Assuming Row 1 contains the percentages and Column G contains the average, I would use:

=SUMPRODUCT($B3:$F3,$B$1:$F$1)+SUM($B3:$F3)*(1-SUMIF($B3:$F3,"<>N/A",$B$1:$F$1))/COUNTIF($B3:$F3,"<>N/A")

 

[elitekatti]

sorry for the formatting issues...

geez.. I wasn't expecting such quick responses!

well, thank you @KyleUK... your solution works perfectly!
could you help me understand the logic here plz!

 

[Aladin Akyurek]

sorry for the formatting issues...

geez.. I wasn't expecting such quick responses!

well, thank you @KyleUK... your solution works perfectly!
could you help me understand the logic here plz!

What happens when Bob has 20,N/A,N/A,N/A,N/A?

 

[elitekatti]

when Bob has 20,N/A,N/A,N/A,N/A.
Then his total is 20 as this subject will have 100% weightage.

 

[KyleUK]

sorry for the formatting issues...

geez.. I wasn't expecting such quick responses!

well, thank you @KyleUK... your solution works perfectly!
could you help me understand the logic here plz!

No worries - I'll run through the logic quickly:

=SUMPRODUCT($B3:$F3,$B$1:$F$1)

SUMPRODUCT is multiplying your scores by your weight and summing them - so all being well (no N/As), this does what you need for weighting.

[COLOR=#574123]+SUM($B3:$F3)*(SUMIF($B3:$F3,"N/A",$B$1:$F$1))/COUNTIF($B3:$F3,"<>N/A")[/COLOR]

This bit handles the N/As (I've rewritten this to be simpler but the idea is the same) - we're taking the total of the scores and multiplying them by whatever %s were N/A, then dividing it by the number of non-N/A scores to redistribute the un-allocated %s. We can do it on the total because you said you wanted to redistribute the un-allocated %s evenly.

Hope that makes sense.

 

[elitekatti]

@KyleUK: Thank you again!

Understood! Makes perfect sense