Weighted Averages ignoring blanks and unrelated columns. For Google Sheets

[bluargent]

Hi!

I am having a lot of trouble calculating a weighted average and ignoring blank cells. I understand what to do if the data is in an array, but I am working on a scorecard that will be printed out. The odd formatting is making it very difficult for me to use the sumproduct function and redistribute the scores.

In essence, I am trying to average 1-3 stakeholder scores and come up with an average score for that particular row. I cannot figure out how to redistribute the score weights if there is a blank cell.

Let me know what I can do!

Thanks,
R

 

[KRice]

That's a little bit tricky since the scores need to maintain their positions in the array, so the corresponding weights need a logic check to ensure there is a score value. This seems to do the trick:

Book1
	E	F	G	H	I	J	K	L	M	N
1	SH1 Score	S1 Weight	S1 Comm	SH2 Score	S2 Weight	S2 Comm	SH4 Score	S4 Weight	S4 Comm	Wted Score
2	2	35%		4	30%		3	35%		2.95
3	3	10%		4	40%		5	50%		4.4
4		10%		4	40%		5	50%		4.5555556
5	3	10%			40%		5	50%		4.6666667
6	3	10%		4	40%			50%		3.8
7	5	10%			40%			50%		5
8		10%		5	40%			50%		5
9		10%			40%		5	50%		5
Sheet3

Cell Formulas
Range	Formula
N2:N9	N2	=LET( scores, FILTER(IF($E2:$L2="","null",$E2:$L2),(ISNUMBER(SEARCH(" score",$E$1:$L$1) )),""), wts, FILTER(IF($E2:$L2="","null",$E2:$L2),(ISNUMBER(SEARCH(" weight",$E$1:$L$1) )),""), IFERROR(SUMPRODUCT(scores,wts)/SUM(wts*ISNUMBER(scores)),""))

 

[bluargent]

Amazing, thank you

The "Filter" function on the variables seems to induce an error. It says there are mismatched range sizes, which seems to be due to the constants $E$1:$L$1 being paired with the lists $E2:$L2 (it's expecting 1 & 8 and getting 1 &1)

I think it's because I am using google sheets, although I am not sure why the function differs

 

[Fluff]

In future when asking questions about Google Sheets, please make that clear in your post/title. Also such questions should be posted in the General Discussion & Other Applications section of the board.
I have done that for you this time.

 

[bluargent]

In future when asking questions about Google Sheets, please make that clear in your post/title. Also such questions should be posted in the General Discussion & Other Applications section of the board.
I have done that for you this time.

Appreciate that, my bad. it if I change the " " in the filter function to $E2:$L2=" " the error goes away, but then it says no matches are found with the filter

 

[KRice]

I wish I had known you didn't intend to use Excel 365 (as suggested by the version showing in your profile and the original forum this was posted in). The problem goes deeper than just FILTER. Sheets requires ArrayFormula wrappers, and the Sheets FILTER doesn't seem to have an option for dealing with an empty array, but I'm not well versed in dealing with Sheets formulas. I'm not aware of a "LET" function in Sheets, so the formula becomes very long, redundant, and difficult to follow, but this works on my system...try this in N2 and copied down:

=SUMPRODUCT(FILTER(ArrayFormula(IF($E2:$L2="","null",$E2:$L2)),ARRAYFORMULA(ISNUMBER(SEARCH(" score",$E$1:$L$1) ))),FILTER(ArrayFormula(IF($E2:$L2="","null",$E2:$L2)),ARRAYFORMULA(ISNUMBER(SEARCH(" weight",$E$1:$L$1) )))) /SUM(Arrayformula( FILTER(ArrayFormula(IF($E2:$L2="","null",$E2:$L2)),ARRAYFORMULA(ISNUMBER(SEARCH(" weight",$E$1:$L$1) )) ) * ArrayFormula(ISNUMBER(FILTER(ArrayFormula(IF($E2:$L2="","null",$E2:$L2)),ARRAYFORMULA(ISNUMBER(SEARCH(" score",$E$1:$L$1) )) ))) ))

 

[bluargent]

I wish I had known you didn't intend to use Excel 365 (as suggested by the version showing in your profile and the original forum this was posted in). The problem goes deeper than just FILTER. Sheets requires ArrayFormula wrappers, and the Sheets FILTER doesn't seem to have an option for dealing with an empty array, but I'm not well versed in dealing with Sheets formulas. I'm not aware of a "LET" function in Sheets, so the formula becomes very long, redundant, and difficult to follow, but this works on my system...try this in N2 and copied down:

=SUMPRODUCT(FILTER(ArrayFormula(IF($E2:$L2="","null",$E2:$L2)),ARRAYFORMULA(ISNUMBER(SEARCH(" score",$E$1:$L$1) ))),FILTER(ArrayFormula(IF($E2:$L2="","null",$E2:$L2)),ARRAYFORMULA(ISNUMBER(SEARCH(" weight",$E$1:$L$1) )))) /SUM(Arrayformula( FILTER(ArrayFormula(IF($E2:$L2="","null",$E2:$L2)),ARRAYFORMULA(ISNUMBER(SEARCH(" weight",$E$1:$L$1) )) ) * ArrayFormula(ISNUMBER(FILTER(ArrayFormula(IF($E2:$L2="","null",$E2:$L2)),ARRAYFORMULA(ISNUMBER(SEARCH(" score",$E$1:$L$1) )) ))) ))

I apologize for not making that clear - this worked perfectly though, thank you very much!

 

[KRice]

That’s good news… I’m happy to help.