If cells in a range are zero prevent sumproduct and sum from DIV/0 error

[DeezNuts]

How can I fix this formula so if none of the 5 cells in the range have a number value this formula doesnt give an error but displays a - instead. The 5 cells show a - when no value is them. J21 is the formula I am having the problem with


Excel 2013

	E	F	G	H	I	J	K
20						Grade
21	-	-	87.00	-	-	87.00	A

<tbody>
</tbody>

​

Worksheet Formulas Cell Formula E21 =IF(COUNT(E3:E20),AVERAGE(E3:E20),"-") F21 =IF(COUNT(F3:F20),AVERAGE(F3:F20),"-") G21 =IF(COUNT(G3:G20),AVERAGE(G3:G20),"-") H21 =IF(COUNT(H3:H20),AVERAGE(H3:H20),"-") I21 =IF(COUNT(I3:I19),AVERAGE(I3:I20),"-") K21 =LOOKUP(J21,AG1:AG18,AH1:AH18) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula J21 {=SUMPRODUCT(AE3:AE7,AF3:AF7)/SUM(IF(AF3:AF7<>"-",AE3:AE7))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Aladin Akyurek]

What is the problem in words, that is, what do you want to calucalate in J21?

 

[DeezNuts]

sorry forgot I had another issue and had moved where this code was getting its data. Here is a mini version of it. The %'s at the top are the weights the blanks between the %'s and the - are where the grades would go


Excel 2013

	R	S	T	U	V	W
24	20%	22%	8%	45%	5%
25
26
27
28
29
30
31	-	-	-	-	-	#DIV/0!

<tbody>
</tbody>

​

Worksheet Formulas Cell Formula R31 =IF(COUNT(R25:R30),AVERAGE(R25:R30),"-") S31 =IF(COUNT(S25:S30),AVERAGE(S25:S30),"-") T31 =IF(COUNT(T25:T30),AVERAGE(T25:T30),"-") U31 =IF(COUNT(U25:U30),AVERAGE(U25:U30),"-") V31 =IF(COUNT(V25:V30),AVERAGE(V25:V30),"-") <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula W31 {=SUMPRODUCT(R24:V24,R31:V31)/SUM(IF(R31:V31<>"-",R24:V24))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Aladin Akyurek]

In words please, not by means of (non-working) formulas...

Try to post an appropriate sample along with the expected results based on that sample.

 

[DeezNuts]

how can i fix this formula so if none of the 5 cells in the range have a number value this formula doesnt give an error but displays a - instead.

{=sumproduct(r24:v24,r31:v31)/sum(if(r31:v31<>"-",r24:v24))}

 

[Aladin Akyurek]

{=sumproduct(r24:v24,r31:v31)/sum(if(r31:v31<>"-",r24:v24))}

What do you have in R24:V24 and in R31:V31? Does "-" mean a real 0?

 

[DeezNuts]

Nevermind I used a iferror figured there was better way of doing it. No idea how to explain it any differently.

=IFERROR( SUMPRODUCT(E2:I2,E21:I21)/SUM(IF(E21:I21<>"-",E2:I2)),"-")

 

[Aladin Akyurek]

Nevermind I used a iferror figured there was better way of doing it. No idea how to explain it any differently.

=IFERROR( SUMPRODUCT(E2:I2,E21:I21)/SUM(IF(E21:I21<>"-",E2:I2)),"-")

Use words, not formulas... Not change references continually...

Just enter:

=IFERROR(SUMPRODUCT(E2:I2,E21:I21)/SUMIF(E21:I21,"<>-",E2:I2),"-")