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

<tbody>
[TD="align: center"]20[/TD]
[TD="bgcolor: #D9D9D9, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #D9D9D9, align: center"][/TD]
[TD="align: center"]Grade[/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #D9D9D9, align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="bgcolor: #D9D9D9, align: center"]87.00[/TD]
[TD="align: center"]-[/TD]
[TD="bgcolor: #D9D9D9, align: center"]-[/TD]
[TD="bgcolor: #548235, align: center"]87.00[/TD]
[TD="bgcolor: #548235, align: center"]A[/TD]

</tbody>

​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E21[/TH]
[TD="align: left"]=IF(COUNT(E3:E20),AVERAGE(E3:E20),"-")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F21[/TH]
[TD="align: left"]=IF(COUNT(F3:F20),AVERAGE(F3:F20),"-")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G21[/TH]
[TD="align: left"]=IF(COUNT(G3:G20),AVERAGE(G3:G20),"-")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H21[/TH]
[TD="align: left"]=IF(COUNT(H3:H20),AVERAGE(H3:H20),"-")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I21[/TH]
[TD="align: left"]=IF(COUNT(I3:I19),AVERAGE(I3:I20),"-")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]K21[/TH]
[TD="align: left"]=LOOKUP(J21,AG1:AG18,AH1:AH18)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J21[/TH]
[TD="align: left"]{=SUMPRODUCT(AE3:AE7,AF3:AF7)/SUM(IF(AF3:AF7<>"-",AE3:AE7))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

<tbody>
[TD="align: center"]24[/TD]
[TD="align: center"]20%[/TD]
[TD="align: center"]22%[/TD]
[TD="align: center"]8%[/TD]
[TD="align: center"]45%[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"][/TD]

[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]26[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]27[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]28[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]29[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]30[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]31[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]#DIV/0![/TD]

</tbody>

​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]R31[/TH]
[TD="align: left"]=IF(COUNT(R25:R30),AVERAGE(R25:R30),"-")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]S31[/TH]
[TD="align: left"]=IF(COUNT(S25:S30),AVERAGE(S25:S30),"-")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]T31[/TH]
[TD="align: left"]=IF(COUNT(T25:T30),AVERAGE(T25:T30),"-")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]U31[/TH]
[TD="align: left"]=IF(COUNT(U25:U30),AVERAGE(U25:U30),"-")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]V31[/TH]
[TD="align: left"]=IF(COUNT(V25:V30),AVERAGE(V25:V30),"-")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]W31[/TH]
[TD="align: left"]{=SUMPRODUCT(R24:V24,R31:V31)/SUM(IF(R31:V31<>"-",R24:V24))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[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),"-")